In this post, I will teach you how to build a Pareto analysis dashboard in Power BI. Specifically, the Pareto principle states that 20% of the causes generate 80% of the effects, and vice-versa.

We can apply this empirical principle to different topics, from work (20% of the efforts cause 80% of the results) to relationships (80% of the dates lead to nothing) and business (20% of products generate 80% of the profits).

In our example, we identify the products driving 80% of the profit and compare them to the remaining products.

Below is what we will build.

Our Power BI Pareto Analysis shows us that 42% of products bring in 79% of margin (top products), while 43% of products bring in 11% of margin (bottom products).

The company in the example has thus the opportunity to refocus its efforts, away from the bottom products. The money and time saved on the bottom products can be used to invest in the top products, or invest in new projects or product launches. 

Load sales data to Power BI for the Pareto analysis

The source data is a sales table by date and product sold, with information on units, sales value, and margin. 

To follow along with the tutorial, you can download the sample sales data from here.

The next step is to create a table with our product classes: top products, middle products, and bottom products.

Create a table for the Pareto classification

Click on Enter Data to create a new table.

The table has a column with the product classes, and a second column to correctly sort the classes. 

If you need help with sorting in Power BI, you can check out the article here.

Now we create a second table, with the P&L accounts for the Pareto analysis. 

Create a table with the P&L accounts to be analyzed 

The table has four rows, one for each account to be analyzed: count of products, units, sales, and margin. This table can have as many lines as you wish to analyze. 

Create the Detail by Class visualization

The first visualization of the dashboard is the Detail by Class. To obtain it, we need to add measures for Rank, Cumulated Margin, and Class.

Add a measure for Rank

We add the measure by clicking on New Measure in the Modeling tab.

Here is the measure for Rank:

Rank = 
RANKX(ALLSELECTED('Sales table'[Product]),CALCULATE(SUM('Sales table'[Margin])),,DESC,Skip)

We use the function RANKX to obtain the product rank: we rank the Product column of the Sales table in descending order, based on the product margin.

The result is the following: Ford has the biggest margin so it has a rank of 1, Chevrolet has the 2^nd biggest margin so it has a rank of 2, and the same logic applies to the remaining products. You can check the screenshot above for an example of the rank.

Add a measure for Cumulated Margin

The cumulated margin is the sum of margin for the current product plus the margin of all products with a lower rank. Below is an example of the cumulated margin.

Ford is the first product (with a rank of 1), thus its cumulated margin is equal to its margin of 3496. Chevrolet is the second product (with a rank of 2), thus its cumulated margin is equal to its margin of 2577 plus the margin of Ford. The cumulated margin for Chevrolet is therefore 6073. 

With the same logic, the cumulated margin for Dodge is equal to the margin of Dodge, plus Ford, plus Chevrolet.

The cumulated margin is useful to find the margin generated by the Top N products. In our example, the Top 2 products generate a margin of 6073, the Top 3 products a margin of 8138, and so on.

The DAX formula for the cumulated margin is below. 

Cumulated margin = 
var currentRank = [Rank]

RETURN SUMX(FILTER(ALLSELECTED('Sales table'[Product]), [Rank]<=currentRank ), CALCULATE(SUM('Sales table'[Margin])))

Now we move on to calculating the class (top, middle, bottom) for each product.

Add a measure for Class

How to calculate a product's Class

To determine which products will be in the top, middle, and bottom classes, we need to decide the percentage of margin going to each class. 

In this tutorial, we follow the standard Pareto principle, and we classify the products generating 80% of the margin as Top products.

We then classify the products generating 90% of margin (but not already included in Top products) as Middle products. The remaining products are the Bottom products.

Below is an example with numbers.

The products A through E make no more than 80% of the margin, therefore they have a class of Top products. Afterward, products F and G make no more than 90% of the margin and therefore have a class of Middle Products. Finally, the remaining products H, I, and J are Bottom products.

DAX formula for Class

Below is the DAX formula for Class.

Class = 
// 1 – defines percentages
VAR topPercentage = 0.8
VAR middlePercentage = 0.9
VAR totalMargin = CALCULATE(SUM('Sales table'[Margin]), ALLSELECTED())

// 2 – finds the ranks separating top class from middle class, and middle class from bottom class
VAR topRankNumber = MAXX(FILTER(ALLSELECTED('Sales table'[Product]), [Cumulated margin]<= totalMargin* topPercentage), [Rank] )
VAR middleRankNumber = MAXX(FILTER(ALLSELECTED('Sales table'[Product]), [Cumulated margin]<= totalMargin* middlePercentage), [Rank] )

// 3 – returns the class of the selected product

RETURN
IF(
OR(ISBLANK([Rank]), ISBLANK(SUM('Sales'[Margin]))), BLANK(),
IF( [Rank]<=topRankNumber,"Top",IF([Rank]<=middleRankNumber,"Middle","Bottom")))

The first part of the Class formula defines the tresholds: 0.8 (which is equal to 80%) for the Top class and 0.9 (which is equal to 90%) for the Middle class.

The second part of the measure finds the rank separating Top products from Middle products (topRankNumber), and the rank separating Middle products from Bottom products (middleRankNumber). With the numbers of the above example, the topRankNumber is 5 (products with rank lower than 5 are Top product). Similarly, the middleRankNumber is 7 (products with rank lower than 7 and higher than 5 are Middle product).

The last part of the formula returns the product class, based on its ranking. 

All the measures are now ready to create the Detail by Class visualization in Power BI.

We can move on to the P&L by class visualization.

Create the P&L by class visualization.

This visualization shows the Product Count, Sales, and Margin by class.

We have 22 products categorized as top products, 8 as middle products and 23 as bottom products.

While the top products generate a margin of almost 30 000, the middle and bottom products both generate around 4 000 in margin.

A new DAX measure ‘Value by Class’ can return all of the information needed for these charts. Below is the formula for it.

Value by Class = 
VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class]= SELECTEDVALUE('Pareto classification'[Product class]))

RETURN SWITCH (SELECTEDVALUE('P&L'[P&L]),
"Count", COUNTROWS(currentClassTable)
, "Units" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Units])))
, "Sales" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Sales])))
, "Margin", SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Margin]))))

There are two parts to the formula. 

The first part of the formula creates a table called ‘currentClassTable’ with the products of the selected class (top, medium or bottom).

The second part of the formula calculates product count, units, sales and margin for the selected P&L line.

Once your measure is ready, we move on to configure the bar chart.
It should be configured as follows: Product class as Axis, and 'Value by Class' as Value.

Finally, we filter the three Bar Charts for the three P&L lines: count, sales and margin.

We can now move to the last and most exciting chart, the Pareto Analysis chart!

Create the Power BI Pareto Analysis visualization.

We need one last measure, which is the percentage by class. The logic is similar to the Value by Class, however in percentage instead of value. The formula is below.

% by Class = 
VAR currentClassTable = FILTER(ALLSELECTED('Sales table'[Product]), [Class]= SELECTEDVALUE('Pareto classification'[Product class]))

RETURN SWITCH (SELECTEDVALUE('P&L'[P&L]),
"Count", COUNTROWS(currentClassTable)/DISTINCTCOUNT('Sales table'[Product])
, "Units" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Units])))/SUM('Sales table'[Units])
, "Sales" , SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Sales])))/SUM('Sales table'[Sales])
, "Margin", SUMX(currentClassTable, CALCULATE(SUM('Sales table'[Margin])))/SUM('Sales table'[Margin]))

The difference compared to the previous formula is that now we divide by the total values, to obtain the percentage by class.

The chart type is ‘Ribbon chart’; we configure it by choosing P&L for Axis and Product Class for Legend.

And we have our Pareto Analysis chart!

I sincerely hope that this tutorial was helpful for you.