Microsoft Office Tutorials and References
In Depth Information
A calculated column is just like any other column in a PowerPivot table and can be used in
rows, columns, filters, or values of a PivotTable. The DAX expression defined for a calculated
column operates in the context of the current row of the table it belongs to. Any reference to
a column returns the value of that column in the current row. You cannot access directly the
values of other rows.
Note As you see later, there are DAX functions that aggregate the value of a column for the
whole table. The only way to get the value of a subset of rows is to use DAX functions that return
a table and then operate on it. In this way, you aggregate column values for a range of rows, and
possibly operating on a different row by filtering a table made of only one row.
Calculated columns are easy to create and use. You have already seen in Figure 3-2 how to
define the GrossMargin column to compute the amount of the gross margin.
[Gross Margin] = Sales[SalesAmount] - Sales[TotalProductCost]
But what happens if you want to show the gross margin as a percentage of the sales amount?
You could create a calculated column with the following formula:
[Gross Margin Perc] = Sales[Gross Margin] / Sales[SalesAmount]
This formula computes the right value at the row level, as you can see in Figure 3-5.
FIguRE 3-5 Calculation of Gross Margin as a percentage calculated row by row.
Nevertheless, when the number is aggregated in the PivotTable, you do not see a correct
result. In fact, the results shown in Figure 3-6 are aggregated using the Sum operation (see
column F, Sum of Gross Margin Perc), which is the default PowerPivot behavior when you
put a numeric column into a PivotTable.
FIguRE 3-6 The Gross Margin Perc column is wrongly aggregated by Sum.