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.