Language: English
Description
The CALCULATE function evaluates an expression with a changed filter context. You can use it in a calculated measure to add filters to the expression, or to remove filters that a pivot table or an enclosing CALCULATE already applies.
To learn how filter context works, see Measure context.
Usage
CALCULATE(EXPRESSION, FILTER1, FILTER2, ...)
| Argument | Description |
|---|---|
EXPRESSION |
The expression to evaluate, for example SUMX(schools, Girls). |
FILTER1, FILTER2, ... |
One or more filters to apply. A filter can be a condition, such as schools.Province == "North", or the ALL function, which clears the filters on a table. |
When you give more than one filter, CALCULATE applies all of them together.
Examples
The examples below use a form with the code schools and the fields Province, Type, Girls and Boys.
Add filters
The following measure calculates the ratio of girls to boys in private schools in the North province:
CALCULATE(SUMX(schools, Girls) / SUMX(schools, Boys),
schools.Province == "North",
schools.Type == "Private")
This gives the same result as a pivot table cell with “North” as the row and “Private” as the column.
Remove filters
The following measure calculates the share of all girls who attend the schools in each cell of a pivot table:
SUMX(schools, Girls) / CALCULATE(SUMX(schools, Girls), ALL(schools))
The pivot table filters apply to the numerator. ALL(schools) clears them in the denominator, so the denominator is always the total number of girls in all schools.