Microsoft Office Tutorials and References
In Depth Information
The reason for the wrong calculation is simple: aggregating the row level gross margin
computes the average above all the transactions and gives 50 percent of a $10 sale the same
weight as a $100 sale. If you look at Figure 3-9, you can understand the issue: to calculate
the right Gross Margin percentage, you must calculate a ratio between aggregated values
of Gross Margin and SalesAmount (which results in 25 percent in the example), instead of
making an average of percentages calculated row by row (which is a wrong 36 percent).
FIguRE 3-9 Compare wrong and right calculations of Margin percentage over two transactions.
So to calculate the right percentage, you need a way to calculate percentage by dividing the
total of gross margin by the total of sales amount. This calculation has to be done at the cell
level of the pivot table and not at the row level of the PowerPivot table. To do that, you need
to take a step further and learn how to define a measure.
A measure is a DAX expression that uses the same syntax as calculated columns; the difference
is the context of evaluation. A measure is evaluated in the context of the cell of the pivot table,
while a calculated column is computed at the row level. The cell context depends on the user
selections on the pivot table. So when you use SUM(SalesAmount) in a measure, you mean the
sum of all the cells that are aggregated under this cell, whereas when you use [SalesAmount]
in a calculated column, you mean the value of SalesAmount in this row .
When you create a measure, you can define a value that changes according to the filter that
the user applies on a pivot table. In this way, you can solve the problem of calculating the
gross margin percentage. To define a measure, you can click the New Measure button on
the PowerPivot tab of the ribbon, shown in Figure 3-10, whenever a cell in a PivotTable is
FIguRE 3-10 New Measure button in the PowerPivot ribbon.