Microsoft Office Tutorials and References

In Depth Information

The availability of ProductSalesAmount makes it possible to define a ProductRankTotal

calculated column that is conceptually identical to the one you saw in the previous section,

using a single table with denormalized data:

ProductRankTotal = COUNTROWS( FILTER( ALL( Products ),

Products[ProductSalesAmount]

> EARLIER( Products[ProductSalesAmount] ) )

) + 1

The main difference is the use of Products instead of a Sales table. So a calculation identical

to that in the previous example is required for ModelSalesAmount and ModelRankTotal

calculated columns:

ModelSalesAmount = CALCULATE( SUM( Sales[SalesAmount] ),

ALLEXCEPT( Products, Products[Model] ) )

ModelRankTotal = COUNTROWS( FILTER( ALL( Products[Model] ),

CALCULATE( VALUES( Products[ModelSalesAmount] ),

ALLEXCEPT( Products, Products[Model] ) )

> Products[ModelSalesAmount] )

) + 1

Also in these calculations, the reference is to a Products table instead of Sales, except for the

reference to the SalesAmount value, which always belongs to the Sales table.

The final results of these calculated columns are visually identical to the Products table you

saw in Figure 9-13.

Tip
Always consider the underlying data model and the uniqueness of the column in the

PowerPivot table before you choose the calculation template to apply for ranking. This rule

is, in general, valid for any calculation that has to aggregate data by using a different selection

than the one made by the user in the PivotTable, as well as for any calculated column that

has to aggregate data other than that reachable through standard table relationships.

Computing ABC and Pareto Analyses

The Pareto principle states that 80 percent of the effects usually come from 20 percent of the

causes. A common application of this principle is that 80 percent of the sales come from 20

percent of the customers (or from 20 percent of the products). This rule is also known as the

80-20 rule. The
Pareto analysis
is a technique based on this principle, and it is used to

categorize items. For example, you might want to determine the fewest number of customers who

account for 80 percent of the sales. These customers might receive particular offers or a

particular service, allowing a bigger investment for retention of these customers than for other

customers.