Microsoft Office Tutorials and References
In Depth Information
The first case is simple because it is really similar to how Excel works with tables. The expression
contained in a calculated column is evaluated once for each row of the table. For each row, the
row context contains the row itself and nothing else. When a row context is active, any
reference to a column of the same table is valid and returns the value of that column for the same
row. In other words, the presence of a row context does not require an aggregation function
because the expression returns just the value of the referenced column in the same row.
In Figure 6-4, you can see that the formula for the calculated column Amount computes
a product, and for each row, the computation is made using the corresponding values of
Quantity and Price in the same row. This is pretty much the same behavior of an Excel table
and is truly intuitive.
FIguRE 6-4 Example of a calculated column.
Let us clarify this process with an example: how is the formula evaluated for row 1 in Figure
6-4? PowerPivot creates a row context containing only row 1 and then evaluates the formula,
which requires the evaluation of Orders[Quantity]. To get the value of the expression, it
searches for the value of the Quantity column in the row context, and this yields a value
of 1. The same evaluation process is necessary for Orders[Price], which in the row context
has the value of 15.
Note The expression defined in a calculated column is evaluated in a row context that is
automatically defined for each row of the table.
There are other cases in which you would like to make a calculation for each row of a table,
but you do not want to create a calculated column. There are many reasons for avoiding
making a calculated column—for example, to create a dynamic expression that changes the
calculation depending on the selection made by the end user in the PivotTable.