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.