Microsoft Office Tutorials and References
In Depth Information
CumulatedProduct = SUMX( FILTER( Product,
>= EARLIER( Product[SalesAmountProduct] ) ),
The preceding expression returns the accumulated value of a product, considering products
in descending order from the top-seller to the worst. In this case, you use the EARLIER
function to get the sales amount for the current product, and the FILTER returns all the rows of the
products that sold at least the amount value of the current product. The technique is very
similar to the one used for the single denormalized table, but you need to sum the value of
SalesAmountProduct instead of the LineTotal of the single transactions because the table you
are aggregating is a subset of the Product table and not of the sales transaction table.
Finally, you have to define the two SortedWeightProduct and ABC Class Product calculated
columns, which are very similar to those defined in the previous example:
SortedWeightProduct = Product[CumulatedProduct] / SUM( Product[SalesAmountProduct] )
[ABC Class Product] = IF( Product[SortedWeightProduct] <= 0.7,
IF( Product[SortedWeightProduct] <= 0.9,
"C" ) )
The only relevant difference is that the denominator of the SortedWeightProduct formula
sums the value of SalesAmountProduct from the Product table instead of LineTotal of the
SalesOrderDetail table. The result would be identical for both these calculations, but it is faster
to aggregate data from a smaller table (Product) than from a larger one (SalesOrderDetail). In
Figure 9-19, you can see the calculated columns created in the Product table.
FIguRE 9-19 Calculated columns created in the Product table for ABC classification on Products.
Finally, you can do the same ABC analysis that you did for the denormalized table used as a
source of data. In this case, you can see in Figure 9-20 the distribution of ABC classification
of products grouped by model. As you might expect, there is a strong relationship, which
indicates you might classify models instead of products, producing very similar results. But
with a few notable exceptions (such as the Road-650 model in row 7), the analysis indicates
there are smaller sales divided among a number of very similar products. From a business
point of view, you could have an important decision to make (classifying ABC for models
instead for products), especially if products differ only in characteristics that do not affect
the way they are produced.