Microsoft Office Tutorials and References
In Depth Information
Performance Issues in ABC Analysis with a Denormalized Table
Please note that calculating ABC classes in a single denormalized table could perform
badly because it requires heavy calculation during data load in PowerPivot. The
normalized approach, using one table for each entity (such as Customers, Products, and
so on) is far better in terms of performance because the calculation depends only on
the size of the related table (such as Products or Customers) and not on the size of the
table that contains the sales transactions.
The CH09-06-AbcSingleTable.xlsx workbook included on the companion DVD contains an
Excel table with Sales data extracted from AdventureWorks by the IT department, as you
can see in Figure 9-14.
FIguRE 9-14 Sales data extracted from AdventureWorks in a single Excel table.
You can import this table as a linked table in PowerPivot. At this point, there are several steps to
obtain the ABC class that you want for each row; each of them is a new calculated column in the
PowerPivot table. Before starting, remember to use the Product and Amount columns in DAX
The first calculated column you define is SalesAmountProduct: this is the total sales amount
for each product. You obtain it by summing up all the sales transactions made for the same
product. So the same value is duplicated for each row of the same product.
SalesAmountProduct = CALCULATE( SUM( Sales[Amount] ),
ALLEXCEPT( Sales, Sales[Product] ) )
To make this calculation, you can use the CALCULATE function, which filters only the rows in
Sales that belong to the same product of the current row context. In fact, the Sales[Product]
in the ALLEXCEPT call does not have a row context provided by the DAX formula, and it uses
the row context used to generate the value of the calculated column for each row of the table.
Note that this complexity is not required in the normalized version of the ABC calculation. In
Figure 9-15, you can see that all transactions of the same product have the same value in the