Dynamic Pareto Chart in Power BI

In this post I want to share my technique of building a dynamic Pareto Chart. Pareto charts are used to show the 80/20 rule according to which roughly 80% of the effects come from 20% of the causes.

As a data set for this post I will use the usual AdventureWorks Data warehouse.

Data Model

After importing the Sales, Date, Customer and Product tables, you are left with the simple star schema:

Building the Matrix

I will build the visual based on the Product Category. The first step to test if our measure gives us the right numbers is to add a matrix visual and drop the Product Category on the rows section and creating the Total Sales measure:

1 |
Total Sales = SUMX( Sales, Sales[Net Price] * Sales[Quantity] ) |

and sorting the matrix in Descending order by Total Sales (I have also switched off the Total Rows as they are not needed)

Now we can start building the **Cumulated %** measure. The first step is to find out which categories have a Sale Amount which is greater than the value displayed in the current filter context. In order to handle categories which could have the same Total Sales amount, we also need find out which categories have a Total Sales amount equal to that displayed in the current filter context but whose Category Name comes alphabetically before the current Category displayed in the matrix. This is accomplished by the following DAX:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
= FILTER ( ALL ( 'Product'[Category Name] ), VAR TableRowSale = [Total Sales] VAR MatrixRowSale = CALCULATE ( [Total Sales], VALUES ( 'Product'[Category Name] ) ) RETURN OR ( TableRowSale > MatrixRowSale, AND ( TableRowSale = MatrixRowSale, 'Product'[Category Name] <= SELECTEDVALUE ( 'Product'[Category Name] ) ) ) ) |

Once we have this table, we can easily calculate the cumulated Total Sales:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
= CALCULATE ( [Total Sales], FILTER ( ALL ( 'Product'[Category Name] ), VAR TableRowSale = [Total Sales] VAR MatrixRowSale = CALCULATE ( [Total Sales], VALUES ( 'Product'[Category Name] ) ) RETURN OR ( TableRowSale > MatrixRowSale, AND ( TableRowSale = MatrixRowSale, 'Product'[Category Name] <= SELECTEDVALUE ( 'Product'[Category Name] ) ) ) ) ) |

And finally divide it by the Total Sales of all Categories:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Cumulated % = DIVIDE ( CALCULATE ( [Total Sales], FILTER ( ALL ( 'Product'[Category Name] ), VAR TableRowSale = [Total Sales] VAR MatrixRowSale = CALCULATE ( [Total Sales], VALUES ( 'Product'[Category Name] ) ) RETURN OR ( TableRowSale > MatrixRowSale, AND ( TableRowSale = MatrixRowSale, 'Product'[Category Name] <= SELECTEDVALUE ( 'Product'[Category Name] ) ) ) ) ), CALCULATE ( [Total Sales], ALL ( 'Product'[Category Name] ) ) ) |

After adding Cumulated % to the matrix you are left with:

Building the Visual

We are now ready to build our visual. Add a ‘Line and Clustered Column’ visual, drop the **Category Name** into the ‘Shared Axis’, the **Total Sales** into the Column Values and the **Cumulated %** into the Line Values. Go to the formatting options of your visual, and make sure that the line Y axis starts at 0 and ends at 1:

Apply your preferred colors and formatting and you are left with:

The chart will be fully dynamic and can be easily filtered by any attribute such as Year, Product Color, Customer Education and so on.

What is your technique of building a Pareto chart in Power BI? Do you know of any custom visual which can make our lives easier?

Books that have helped me a lot in my **DAX** journey:

Hi there! Such a great article, thank you!

Hi Leon – thank you for the feedback!

Excellent, it has been VERY helpful. I’ve been looking for hours to find a solution for a Dynamic Pareto Chart on Power BI that does not mess up with dates.

However, just want to indicate that there is a typo in one of your formulas which was making me get an inverted chart.

In the Total Sales of all Categories, line 12 it should be :

TableRowSale < MatrixRowSale,

"”

Otherwise that is perfect !

Thank you Paula! Your feedback is appreciated!

Timely, and good information. I am using this with a total qty, not sales. There are several that are the same qty, and resulting in the same cummulated%. I cannot determine how to “fix” this. Any insight? Thank you.

Hi Teresa!

Normally what you’d need to modify is:

Total Sales = SUMX( Sales, Sales[Net Price] * Sales[Quantity] )To

Total Quantity = SUM(Sales[Quantity])and then use [Total Quantity] in the final measure instead of [Total Sales]

This change does not provide the correct results?

This is exactly what I was looking for. Thank you so much for this post.

Thank you for the kind words, Jamie!