Microsoft Office Tutorials and References
In Depth Information
FIguRE 9-16 CumulatedProduct aggregates all the products that sold more than the current one.
The third calculated column is SortedWeightProduct: this calculation simply transforms the
CumulatedProduct value into a percentage. You use this number to filter all the transactions
according to the percentage limit that corresponds to ABC classes. (In this case, you use 70
percent for Class A, up to 90 percent for Class B, and the remaining for Class C).
SortedWeightProduct = Sales[CumulatedProduct] / SUM( Sales[Amount] )
Finally, you can define the last calculated column, named ABC Class Product: this is the final
result of the calculation. Depending on the value of SortedWeightProduct, an A, B, or C is
displayed. Remember that a percentage is expressed by a number between 0 and 1.
[ABC Class Product] = IF( Sales[SortedWeightProduct] <= 0.7,
IF( Sales[SortedWeightProduct] <= 0.9,
"C" ) )
In Figure 9-17, you can see the final result, with an ABC class defined for each transaction.
FIguRE 9-17 ABC Class Product defines the ABC class for each transaction based on SortedWeightProduct.
At this point, you can browse the data with a PivotTable in Excel. In Figure 9-18, for example,
you can look at the relevance of the ABC class of products for each customer. (In this case,
customers are sorted by total of sales in descending order.)
FIguRE 9-18 A PivotTable splits customer sales by the ABC class of products.