Applying the Pareto Principle: Categorizing Products for Sales Analysis in Power BI

The Pareto principle says that about 80% of the effects come from 20% of the causes. In this article, I’ll create a Power BI measure to rank products from Adventure Works into 4 categories, to show that a few products represent most of the sales from the FactResellerSales table. It’s similar to the Pareto’s principle, but I feel it brings more insights having 4 categories rather than 2.

• A = First 70% of sales
• B = Next 20% of sales
• C = Last 10% of sales
• D = 0% of sales

Adventure works dataset

I imported this model from Adventure Works SQL database.

To solve this business need, we need to, for each product, compute its sales amount, then add the sales of all products that have higher sales, then compare the result with the sales amount for all products, and finally assign a category based on the ratio.

We could do this with calculated columns, but as I want to change the result dynamically, based on slicer selection on product category and sub-category, I’ll create a measure to solve this problem.

Building the Pareto formula

Here’s the solution decomposed. As I said, to solve this business need, we need to, for each product (EnglishProductName), compute its sales amount (SumSalesAmount), then add the sales of all products that have higher sales (ABCD Class IncrementalSalesAmount), then compare the result with the sales amount for all products (ABCD Class TotalSalesAmount), and finally assign a category (ABCD Class) based on the ratio (ABCD Class IncrementalPercent).

The table below shows that the measure ABCD Class works with the selection of a product category and a product sub-category.

Putting this all together, we have the following DAX Code

CurrentSumSalesAmount references a measure on the FactResellerSales table: SumSalesAmount = SUM(FactResellerSales[SalesAmount]). On the table, CurrentSumSalesAmount has row context, and thus we have the sum of sales amount for each product.

TotalSalesAmount computes the total sales amount of selected products. We need ALLSELECTED to remove the filtering on each product. ALLSELECTED returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside. That’s why we have the grant total for each row.

IncrementalSalesAmount computes, for each product row, the total sales for the current product row, plus the sales for any product with higher sales than the current product row. We use SUMX because we want to compute the sum of an expression evaluated for each row in a table. To create the table in DAX, we have to use ALLSELECTED ( DimProduct[EnglishProductName] ) because we want to remove the filtering on product created by the table. We then filter this table to have all products with higher or equal sales than the sales related to the current row. The sales related to the current row is stored in the variable CurrentSumSalesAmount, and that’s why we’re using it here.

The rest of the code is straightforward as it doesn’t involve any DAX specific formula. We compute the ratio between the IncrementalSalesAmount and the TotalSalesAmount, IncrementalSalesPercent, and we assign a category based on this ratio (the Result variable).

It’s interesting to notice that if you have a high sales product in the last non-empty row, the ABCD class, with this formula, will assign the C class, not B. In this case, the Sport-100 Helmet, Red, has been assigned the C category even though it counts for 31%.