Microsoft Office Tutorials and References
In Depth Information
That calculation is similar to the CumulatedProduct of the previous example. The only
difference is the filter condition because the column to be aggregated is always SalesAmountProduct.
(Remember that the FILTER function always operates on the Products table, and for each model
there are many products in that table.)
Finally, you can define SortedWeightModel and ABC Class Model calculated columns. These
definitions are almost identical to those you saw in the previous example and do not require
SortedWeightModel = Product[CumulatedModel] / SUM( Product[SalesAmountProduct] )
[ABC Class Model] = IF( Product[SortedWeightModel] <= 0.7,
IF( Product[SortedWeightModel] <= 0.9,
"C" ) )
In Figure 9-21, you can see an excerpt of the resulting Product table, which has the new
calculated columns required for ABC classification of product models.
FIguRE 9-21 Calculated columns in the Product table for ABC classification of models.
In Figure 9-22, you see a PivotTable that shows the total for each product model crossed
with ABC classification of product models. This time, each product model has a value for
only one class, which is the one the product model belongs to.
FIguRE 9-22 A PivotTable splits product model sales according to ABC classification of models.