# Creating A Pareto Chart In Power BI – Advanced DAX

This blog tutorial showcases a really interesting example around Pareto analysis. Iâ€™m going to teach you how to create a Pareto chart using advanced DAX formulas. You may watch the full video of this tutorial at the bottom of this blog.

I’ll show you how to use a combination of formulas to be able to generate a visualization like this one.

The first thing that I always recommend when working on something similar with this analysis is to turn the visualization into a table. This way, you can really examine the numbers that are needed to generate the said visualization.

For this scenario, you first need to create a cumulative total to get the Total Revenue amount. As you can see, there are no dates and numbers. Therefore, it will be challenging to generate a cumulative total that’s based on text value instead of numbers. I’ll teach you how to do this step by step.

## Calculating The Cumulative Total

The first thing that Iâ€™m going to show you is the calculation for cumulative total without dates.

The technique here is to use the SUMX function as well as an iterator like the FILTER function. The FILTER function goes inside the SUMX. After that, you need to create a virtual table using the SUMMARIZE function.

The virtual table needs to look at all the sales inside the selected date context. After that, it should go through the specific state codes inside the table.

The said part of the formula will create a table exactly like the sample table. The only difference is itâ€™s creating the table virtually for now.

The next part of the formula will create another virtual table for the revenue. But because this logic is inside a filter, it manipulates the table virtually to create a cumulative total. The formula should iterate through every row and part of the total revenue table.

If the revenue of the specific row is greater than or equal to the state revenue, it calculates the revenue amount and brings it into the table.

After that, you need to add the variable, VAR StateRev, so that itâ€™s going to evaluate the Total Revenue.

That’s how you create a cumulative total even though thereâ€™s no ranking based on dates or numbers. The cumulative totals that were created are only based on a text column.

All the cumulative totals in the table are calculated because they all evaluated to true, and then the total revenues are aggregated to the cumulative total.

## Creating The All Sales Table

The next part that I’d like to demonstrate is the creation of the sales table for the pareto chart.

First, click New Measure, and then type in the name of the measure. In this case, it’s All Sales. Then, start the formula with the CALCULATE function to work out the Total Revenue. And then, go ALLSELECTED (Sales).

Click enter, and drag the completed measure into the table to see the corresponding data.

In addition to this, you can create a Pareto chart based on any selection through the Product Category selector.

## Combining DAX Formulas To Create Pareto Chart

The next thing that I want to show you is how to combine the previous formulas into another one by simply adding some DAX functions.

To do that, just create a new measure or copy an existing (similar) one, then rename it accordingly. For this example, the new measure is named as Pareto Chart (States).

As you can see, there’s already an existing formula in the copied measure so you only need to add some DAX formulas. In this case, you need to add some variables using the VARIABLE function, which simplifies calculations.

The first variable (VAR StateRey) simply equals to Total Revenue, while the second one (VAR AllSales) uses the CALCULATE and ALLSELECTED functions.

For the logic, add the DIVIDE function before the SUMX, and then add the variable, AllSales on the bottom part. It’s important to have a constant value for the results to return. Thus, you should put zero as the alternative result.

There are times when the results appear in a number format, but the Pareto Chart (States) column should show in percentage like this one below.

Thus, you need to go to Measure tools, and then change the format into a percentage.

Once you go back to the table, you can see the exact results in the table transform into a Pareto chart. That’s the reason why I always recommend having it inside a table first before creating the chart.

You can experiment around the chart and select dynamic data in the Product Category selector. As you can see, the Pareto chart also changes dynamically as per your product categories.

You can also add some colors to your Pareto chart using some data labels to make it more compelling. Just take time to play around the formatting area and set up your Pareto chart accordingly. For instance, you can change the visualization and enable the Line Values like this one below.

There’s so much you can do inside Power BI to make your data stand out and your report more effective and visually appealing.

## Conclusion

I hope you like this example of formula combinations. It can get way too advanced, but itâ€™s all doable in Power BI. Iâ€™d also like to mention that the VAR (variable) function really helped us in this example.

The key is understanding the right time to use variables in the evaluation. It could either be used at the start of the formula or as an iterating function.

I hope that you got something out of this tutorial and hopefully you can find a way to utilize the sample formula pattern into your own models.

All the best!

Sam

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginnerâ€™s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...