Microsoft Office Tutorials and References
In Depth Information
FIguRE 9-20 A PivotTable splits product model sales by ABC classification of products.
ABC with Denormalized Attributes on Normalized Tables
In the previous example, the model of a product was described in the ProductModel table,
which was referenced by the Product table. However, you might have a scenario in which
you want to build an ABC classification for an attribute of the Product table. For example,
the model might be a column in the Product table, without being normalized in a
separate table. In this case, you need to use a mix of the techniques that we have used in the
previous two examples. The Model column is your key to identifying the granularity of ABC
First of all, you still calculate the SalesAmountProducts column by using the same
expression we described in the previous scenario. You can find this model in the
CH09-08-AbcDenormalizedAttributes.xlsx workbook included on the companion DVD.
SalesAmountProduct = CALCULATE( SUM( SalesOrderDetail[LineTotal] ) )
You need this calculation to define the SalesAmountModel calculated column, which
aggregates the sales of all the products belonging to the same product model:
SalesAmountModel = SUMX( FILTER( Product,
Product[Model] = EARLIER( Product[Model] ) ),
Product[SalesAmountProduct] )
In the SalesAmountModel definition, the EARLIER function gets the model name of the
current row (a single product) and the FILTER function returns all the products of the same
model. At this point, you can define the CumulatedModel calculated column:
CumulatedModel = SUMX( FILTER( Product,
>= EARLIER( Product[SalesAmountModel] ) ),
Product[SalesAmountProduct] )
Search JabSto ::

Custom Search