Microsoft Office Tutorials and References
In Depth Information
If you are facing a regular nonadditive measure, one solution might be to change the function
used by PowerPivot to perform the aggregation.
Note Beware that this solution works only for nonadditive measures and does not address the
problem of semiadditive ones, which we already explained in Chapter 7.
As an example (see CH08-05-OrdersWeight.xlsm companion workbook), let us consider a
PowerPivot model in which we gather for each line of the orders, the weight of the item
shipped, along with some other useful columns that you can see in Figure 8-14.
FIguRE 8-14 Sample data model with order lines and weight.
You can easily build a PivotTable on this model and produce the report in Figure 8-15 where,
for each month, we summarize the total number of orders (computed using a measure that
performs the distinct count) and the weight.
FIguRE 8-15 A sample PivotTable showing number of orders and weight.