Microsoft Office Tutorials and References

In Depth Information

ABC Analysis with Multiple Normalized Tables

In this second example, you use five normalized tables; you import from AdventureWorks the

following tables: SalesOrderHeader, SalesOrderDetail, Contact, Product, and ProductModel.

The CH09-07-AbcMultipleTables.xlsx workbook included on the companion DVD contains the

PowerPivot model with these tables already loaded.

Tip
If you try to make the import by yourself, remember to exclude columns that are

not supported by PowerPivot, such as Instructions from the ProductModel table and

AdditionalContactInfo from Contact.

In this case, you define the same calculated columns for ABC classification in the Product

table instead of the fact table. You use the ProductID column to identify the product and

LineTotal (from SalesOrderDetail table) as the measure to use for ABC classification.

This is the definition for the SalesAmountProduct calculated column in the Product table:

SalesAmountProduct = CALCULATE( SUM( SalesOrderDetail[LineTotal] ) )

In this case, each calculation is different for each row because each row is a single product.

The CALCULATE function makes use of the existing relationship between SalesOrderDetail

and Product tables, avoiding the need for a filter condition in the CALCULATE function, such

as the ALLEXCEPT one used in the previous example with a single denormalized table.

Slower Alternatives to CALCuLATE

An alternative to the CALCULATE function worth mentioning is based on the use of SUMX

and RELATEDTABLE. It might appear easier if you are used to working with a relational

database, but it is slower than CALCULATE in PowerPivot. For example, consider the following

definition for SalesAmountProduct calculated column:

SalesAmountProduct = SUMX( RELATEDTABLE( SalesOrderDetail ),

SalesOrderDetail[LineTotal] )

The RELATEDTABLE function makes use of the existing relationship between

SalesOrderDetail and Product tables. However, this is the same logic used by

CALCULATE. The only reason to use this version based on SUMX would be if you

need to define a more complex calculation that would have to be made on every

single row of SalesOrderDetail table without defining a calculated column with such

an expression. (The SUM function used in the CALCULATE version accepts only a

single column as a parameter.)

The second definition (shown on the following page) is for the CumulatedProduct calculated

column, which is always defined in the Product table.