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 ),
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.