Microsoft Office Tutorials and References

In Depth Information

In the example, the ALL function receives a column reference as a parameter (previously we

used a table reference) and returns all the values from that column, regardless of the existing

filter context. In other words, using CALCULATE and ALL with one or more column parameters

clears the existing filter context for those columns for the expression passed to and evaluated

by the CALCULATE function.

The AllChannels measure defined in Figure 6-13 returns the total for all the channels, even if

you have a different selection in the PivotTable.

FIguRE 6-13
The AllChannels measure calculates amount by ignoring the Channel selection.

Important
When you use CALCULATE, you can replace SUMX with SUM whenever the expression

of SUMX is made of a single column. In other words, these two expressions are equivalent:

CALCULATE(
SUMX( Orders, Orders[Amount] ),
ALL( Orders[Channel] ) )

CALCULATE(
SUM( Orders[Amount] ),
ALL( Orders[Channel] ) )

You still need SUMX whenever the expression that has to be aggregated contains more terms:

CALCULATE( SUMX( Orders
, Orders[Quantity] * Orders[Price]
),

ALL( Orders[Channel] ) )

In that case, you do not have an alternative syntax based on SUM, unless you move the expression

in a calculated column, just like the Amount column in the Orders table.

CALCULATE is a fundamental function for operating on the filter context; it calculates rows

that are not part of the current selection but that are needed to make comparisons, ratios,

and so on.

Finally, we should mention that if you want to remove filters from all but a few columns in

a table, you can use ALLEXCEPT. In other words, using the Orders table as an example, the

following statements are equivalent:

CALCULATE( SUM( Orders[Amount] ),

ALL( Orders[Channel], Orders[Color], Orders[Size],

Orders[Quantity], Orders[Price], Orders[Amount] ) )

CALCULATE( SUM( Orders[Amount] ),

ALLEXCEPT( Orders, Orders[City] ) )