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] ) )
Search JabSto ::

Custom Search