Livio/ November 27, 2018/ DAX, Power BI/ 0 comments

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:

Pareto Chart Power BI

 

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:

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

Pareto Chart Power BI

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:

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

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

 

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

Pareto Chart Power BI

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:

Pareto Chart in Power BI

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

Pareto Chart in Power BI

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? 

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*