CALCULATE

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.

Next item
GREAT_CIRCLE