Microsoft Office Tutorials and References
In Depth Information
To understand better what happened, let us look at the exact sequence of operations
performed to calculate the value in cell G5, which is highlighted in Figure 6-8:
■ The filter context is defined by the coordinates of cell G5, which are <Green, Internet>.
■ The value required is CalcAmount, which is the measure defined with this expression:
SUMX( Orders, Orders[Quantity] * Orders[Price] ).
■ The SUMX function iterates all the rows of the Orders table that are active in the filter
context. So only the two rows highlighted in Figure 6-9 are going to be iterated.
FIguRE 6-9 Rows iterated by SUMX to calculate cell G5 of the PivotTable.
■ For each of these two rows, the calculation Orders[Quantity] * Orders[Price] is performed
using that row as the row context.
■ The resulting values of these two rows (192 and 128, respectively) are aggregated
together, summing them up, because we are using SUMX.
■ The final result of 320 is returned by SUMX and is used to ill the G5 cell.
You have a set of functions that show the same behavior as SUMX but use a different
aggregation criterion. These functions are named with the name of the aggregation operation and end
with an X character.
An important concept to remember is that these functions add a row context to the existing
filter context, making an iteration on all the rows that are included in the filter context and
activating a row context for each of these rows.