Microsoft Office Tutorials and References
In Depth Information
You can try to change the aggregation formula to Average using the Summarize By context
menu for the item in the Values list of the PowerPivot Field List. (See Figure 3-7.)
FIguRE 3-7 Average is selected in the Summarize By context menu for the item in the Value list.
Nevertheless, even using the Average aggregator, the final result is not yet the right one. In
fact, in Figure 3-8 you can see that the average of Gross Margin Perc (column F) is internally
calculated as the following:
= SUM( Sales[Gross Margin] / Sales[SalesAmount] ) / COUNTROWS( Sales )
That number is not equal to column G, which is calculated in this other way:
= SUM( Sales[Gross Margin] ) / SUM( Sales[SalesAmount] )
This last effort is the correct way to perform the calculation of this average.
FIguRE 3-8 The GrossMarginPercentage calculated column is different from column G calculated in Excel.