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 ),
> 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
Search JabSto ::

Custom Search