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

formulas.

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

SalesAmountProduct column.