Microsoft Office Tutorials and References
In Depth Information
The Pareto principle is also the basis for a similar type of classification known as ABC analysis .
The concept is to classify items (usually products) using three classes:
■ Class A, which contains items that account for 70 percent of the total value
■ Class B, which contains items that account for another 20 percent of the total value
■ Class C, which contains items that account for the remaining 10 percent of the total value
This classification may vary in its definition—for example, someone uses different percentages
for ABC classes (80/15/5 instead of 70/20/10) and someone makes a division based on the
number of items (for example, 20/30/50 for classes A/B/C).
The ABC analysis is used to create an attribute that can be used to filter items (like Products
and Customers). In other words, if a product belongs to Class A, you want to add a column
to the products table that contains A so that it can be used in slicers, filters, rows, and
columns of a PivotTable. For this reason, you cannot rely on measures to perform ABC analysis.
The calculation for ABC analysis can be made in PowerPivot by using calculated columns. In
this way, each row can have an attribute with the appropriate ABC class. The ABC calculation
has to be made in consideration of a particular grouping and sort order.
Suppose you want to define an ABC class of Products for sales transactions of
AdventureWorks. You might want to classify Products in this way:
■ Products that make 70 percent of the sales are in Class A.
■ Products that make 20 percent of the sales are in Class B.
■ Products that make 10 percent of the sales are in Class C.
Conceptually, you need to group sales transactions by product, sort products by sales amount
in descending order, calculate a sort of running sum of products following that order, and define
the class for each product by comparing that running sum with the boundaries of the classes
(70 percent and 90 percent of sales amounts for all transactions correspond to boundaries of
Class B). In the following examples, you see how to implement ABC analysis for Products in
several PowerPivot data models.
ABC Analysis with a Single Denormalized Table
In this first example, you use Sales data in a single table, which is also called a denormalized
table. This case might occur whenever you use a query (or a view provided by the IT
department) to extract data in a single table. This is a very convenient way to work on data in Excel
without PowerPivot. For that reason, you might already have data in this format. However,
consider that having data separated in multiple tables is a more convenient way to use PowerPivot,
as you will see later.