Pareto Analysis in Power BI

Level: Intermediate

Pareto Analysis is a statistical technique that applies the Pareto Principle to data. This is more commonly known as the 80:20 Rule. The Pareto Principle is based on the presumption that a relatively small number of inputs (20%)  have most impact on the results/output (80%).  The 80:20 rule can be applied to a wide variety of data in most businesses.  Examples include:

• Which 20% of products make up 80% of sales
• Which 20% of customers make up 80% of profit.
• just to name a few

Although it is called the 80:20 Rule, the results normally do not come out exactly in the ratio of 80:20.  Anything close to this ratio is considered normal.  Armed with the results, you can focus business efforts on the 20% of the things that impact 80% of the results.

The Pareto Principle can be illustrated on a Pareto chart. In Power BI you can use a combo line and column chart. The column in the chart represents the individual inputs, while the line chart represents the cumulative percentage of the inputs as they build towards 80%.

In order to show you how to build a Pareto chart in Power BI, I will use in this article the AdventureWorks database (as usual) to show that just 12% of the products (in this case) contribute to 80%of the total margin for the business.

Measures Used for A Pareto Chart

To start with I have created 3 measures in the Sales table.

Total Cost = SUM(Sales[TotalProductCost])

Total Sales = SUM(Sales[ExtendedAmount])

Total Margin = [Total Sales] – [Total Cost]

Next I needed to write the measure that would calculate the cumulative % of sales ranked from the best product to the worst.  This is a tricky formula, but I explain how it works below.

Cumulative % = 
VAR thisProductMargin = [Total Margin] /* this sets the margin for the current product 
                                          (the one that is the current column in the column chart) */
VAR TotalMarginAllProducts =
    CALCULATE([Total Margin], ALL(Products)) /* the total margin across all products.  
                                              This is needed to turn the absolute margin into a percentage */
RETURN
    CALCULATE(
        [Total Margin],
        FILTER(ALL(Products), [Total Margin] >= thisProductMargin)
    ) / TotalMarginAllProducts

To understand how this formula works, I will need to refer to the results table shown below showing the product codes, Total Margin in $ for each product, and the Cumulative % of margin.  Note that the table is sorted from highest margin $ product to the lowest margin $ in descending order.

The first row in the table below is product 312.  It has a margin of approximately $474,151 which makes up 4% of the total margin for all products.  Each row in the table below is calculated completely independently of each other for both the Total Margin and Cumulative % measures.

Now, let me explain how a single row is calculated (the row highlighted in red below – ProductKey 363).

The highlighted row in the table below is filtered for ProductKey = 363. This is what the row section of a table does – it filters the data before the measure is calculated.

Line 2 in the measure above is a variable.  Variables are defined first as part of a measure before any other execution is completed.  So the variable thisProductMargin is assigned a value of $437,021 in this case.

Line 4 in the measure above is pretty straight forward.  It uses CALCULATE to remove the filter on ProductKey = 363 so it can calculate the total margin for all products.  The ALL function removes all filters on products.

The secret sauce of the formula is line 10.

• • Filter is an iterator and hence it has a row context (it can repeatedly evaluate a formula, one row at a time, as it steps through the specified table).  In this case it is going to step through the products table.

• • But there is a filter on ProductKey = 363, so FILTER needs to step through ALL(Products) instead of Products.  ALL removes the filter applied by the visual before FILTER starts it’s iteration process.  If I didn’t use ALL here, then there would be only 1 row in the table. ProductKey = 363 due to the filter coming from the visual.

• • Then FILTER steps through the unfiltered copy of the products table and checks each product, one at a time, to see which  individual products have a margin greater than the current product.  The current product is ProductKey = 363.

• • If  FILTER comes to a product that has a margin greater than Product 363, that product is kept – in other words that product forms part of the cumulative build of all products that make up the cumulative total.

• • FILTER continues to check every single product against this criteria.

• • At the end of the filter process, only products with a total margin greater than or equal to the product being compared (product 363 in this case) are kept.

• • Then the total margin for all those products is calculated.

• • Finally this total is divided by the total for all products.

The process then repeats for every single row in the visual, over and over again.

Creating a Pareto Chart in Power BI

As I mentioned above, we can use the line and clustered column chart visual to display the Pareto chart.  To do that:

• • Add a Line and clustered column chart visual to the Report canvas.

• • Put ProductKey from the Products table on Shared axis.

• • Put Cumulative % from the Products table on Line values.

• • Place the [Total Margin] measure on Column values.

• • Sort the chart based on Total Margin, Sort Descending.

This will sort the columns in the chart in descending order as you can see above. I then went ahead and wrote a measure to count the number of products that make up the 80% of margin.  That measure is as follows.

Count of Products Making 80% of Margin =
     CALCULATE(
         DISTINCTCOUNT(Sales[ProductKey]),
         FILTER(Products, [Cumulative %] <= 0.8)
     )

Here is my Pareto Analysis workbook if you’d like to take a look and learn more about the use of Pareto chart in Power BI.