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.

Measures

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

selected.

FIguRE 3-10
New Measure button in the PowerPivot ribbon.