How to Create a Pareto Chart in Power BI Using DAX

Pareto Chart in Power BI Using DAX

Welcome back to another practical blog on Power BI With Akshay, where I try to explain Power BI and DAX concepts through real-world business scenarios. In this blog, we are going to explore Pareto Chart in Power BI.

Have you ever looked at a long list of products and wondered which ones are actually driving most of the business profit?

Or noticed how, in many successful teams, only a few top performers contribute to a large portion of the overall result?

This is exactly the kind of question that Pareto Analysis helps us answer.

By the end of this tutorial, you will know how to create a dynamic Pareto chart in Power BI using DAX, calculate cumulative profit percentage, apply the 80/20 rule, and identify the products that contribute the most to overall profit.

And don’t worry. I’ll explain the DAX step by step so that you understand not just what the measures do, but why they work.

🚀 Here is a short demo video demonstrating the Pareto chart in Power BI:

If you enjoy practical Power BI tutorials like this one, don’t forget to subscribe to my Power BI With Akshay YouTube channel.

What Is Pareto Analysis?

Pareto Analysis is a decision-making technique based on the Pareto Principle, more commonly known as the 80/20 Rule. The principle suggests that a relatively small number of causes often produce a large percentage of the results.

In simple words:

Roughly 80% of the outcomes come from 20% of the causes.

One important thing here is that the 80/20 split should not be treated as a strict mathematical rule. Real-world data will not always produce an exact 80% and 20% distribution.

You may find that 30% of your products generate 75% of your profit, or that 15% of your customers contribute 85% of your revenue. That is completely normal. The real purpose of Pareto Analysis is to identify the small group of items making the biggest contribution.

Real-World Examples of the Pareto Principle

The Pareto Principle can be applied almost everywhere:

Products: A small number of products may generate most of the profit.
Customers: A limited group of customers may contribute most of the revenue.
Quality analysis: A few defect categories may cause most customer complaints.
IT support: A small number of recurring issues may generate most support tickets.
Project management: A few critical activities may create most of the business value.
Personal productivity: A limited number of tasks may produce most of your meaningful output.
Sports: A few top-performing players may contribute significantly to a team’s success.
Exams: A smaller portion of the syllabus may appear in a larger percentage of the questions.

I could keep adding examples because the applications are almost endless.

Why Use Pareto Analysis?

A normal bar chart can show which products have the highest profit, but a Pareto chart goes one step further. It helps us understand how quickly those products combine to reach a specific percentage of the total result.

This allows businesses to:

Identify the most valuable products or customers.
Prioritize areas that can have the greatest impact.
Allocate time, budget, and resources more effectively.
Investigate low-performing products.
Focus improvement efforts on the most important causes.
Make decisions based on contribution rather than assumptions.

Now that we understand the idea behind Pareto Analysis, let’s implement it in Power BI.

Creating a Pareto Chart in Power BI

For this tutorial, I’m using the same sales dataset that I have used in some of my previous blogs.

Here is a snapshot of the Sales Data table:

Sales Data Table for Pareto Chart

For the analysis, we will use a Line and Clustered Column chart. The columns will display the profit generated by each product, while the line will display the cumulative profit percentage.

We will also add a reference line at 80% so that we can quickly identify the products contributing approximately 80% of the total profit.

What Is the Aim of This Analysis?

Our objective is simple:

Identify the top products that collectively generate approximately 80% of the total profit.

This insight can help the business understand which products deserve more attention. However, we should be careful before deciding to discontinue the remaining products. A lower-profit product may still be strategically important, attract key customers, support another product, or perform well in a particular region.

A Pareto chart should guide further analysis, not replace business judgment.

Step 1: Create the Total Profit Measure

Let’s begin with a base measure for total profit.

Total Profit =
SUM ( 'Sales Data'[Total Profit] )

Now add a Line and Clustered Column chart to the report.

Configure the visual as follows:

Add Item Type to the X-axis.
Add Total Profit to the Column Y-axis.
Sort the visual by Total Profit in descending order.

The descending sort order is very important.

A Pareto chart should begin with the highest-contributing product and move toward the lowest-contributing product. Otherwise, the cumulative percentage line will not represent the Pareto distribution correctly.

Total Profit by Product

At this stage, the chart displays every product and its corresponding profit. Now we need to calculate the cumulative profit.

Step 2: Create the Cumulative Profit Measure

This is the core part of the Pareto analysis. The objective is to start with the highest-profit product and continue adding the profit of each lower-ranked product.

Use the following measure:

Cumulative Profit =
VAR _CurrentProfit =
    [Total Profit]
VAR _ProductTable =
    ADDCOLUMNS (
        ALLSELECTED ( 'Sales Data'[Item Type] ),
        "@ProductProfit", CALCULATE ( [Total Profit] )
    )
RETURN
    SUMX (
        FILTER (
            _ProductTable,
            [@ProductProfit] >= _CurrentProfit
        ),
        [@ProductProfit]
    )

Understanding the Current Profit Variable

The _CurrentProfit variable captures the profit of the product currently being evaluated in the chart.

For example, when the chart evaluates Cosmetics, this variable contains the profit for Cosmetics. When it evaluates Household, it contains the profit for Household.

Creating the Virtual Product Table

VAR _ProductTable =
    ADDCOLUMNS (
        ALLSELECTED ( 'Sales Data'[Item Type] ),
        "@ProductProfit", CALCULATE ( [Total Profit] )
    )

This part creates a virtual table containing the currently selected products and their respective profit values. The use of ALLSELECTED is important because it allows the calculation to consider the products available after external filters and slicers have been applied.

For example, if a user selects only Asia and Europe through a Region slicer, the product profit values will be evaluated within those selected regions. This makes our Pareto analysis interactive and suitable for real report scenarios.

Calculating the Cumulative Profit

SUMX (
    FILTER (
        _ProductTable,
        [@ProductProfit] >= _CurrentProfit
    ),
    [@ProductProfit]
)

The FILTER function keeps all products whose profit is greater than or equal to the profit of the current product.The SUMX function then adds the profit for those products.

As we move from the highest-profit product to the lowest-profit product, more products become part of the filtered table. This creates the cumulative profit.

If you want to understand cumulative calculations in more detail, read my separate blog on running totals in Power BI, where I explain different DAX approaches, including the WINDOW function.

Step 3: Create the Cumulative Profit Percentage

The cumulative profit tells us the combined profit generated up to each product. However, to identify the 80% threshold, we need to express that cumulative value as a percentage of the total profit.

Create the following measure:

Cumulative Profit % =
VAR _SelectedProductProfit =
    CALCULATE (
        [Total Profit],
        ALLSELECTED ( 'Sales Data'[Item Type] )
    )
RETURN
    DIVIDE (
        [Cumulative Profit],
        _SelectedProductProfit,
        BLANK ()
    )

The _SelectedProductProfit variable calculates the total profit across all products currently available within the user’s selection. The DIVIDE function then divides the cumulative profit by the selected total profit.

Using DIVIDE instead of the / operator is generally safer because it allows us to handle a zero or blank denominator.

After creating the measure, set its format to Percentage. You may also display one or two decimal places, depending on the level of detail you want in the report.

With this, our DAX measures are ready. Now it’s time to bring the Pareto chart to life.

Step 4: Build the Pareto Chart

Return to the Line and Clustered Column chart and configure it as follows:

Keep Item Type on the X-axis.
Keep Total Profit on the Column Y-axis.
Add Cumulative Profit % to the Line Y-axis.
Sort the products by Total Profit in descending order.
Format the line axis as a percentage.
Add an 80% reference line from the Analytics or reference-line settings.
Turn on useful tooltips and data labels.

The columns now show the individual profit generated by each product. The line shows how the cumulative percentage increases as more products are included.

Pareto Chart Build Properties

Pareto Chart Build properties

After making a few formatting changes, the final Pareto chart should look something like this:

Final Pareto Chart in Power BI

How to Read the Pareto Chart

Now that our Pareto chart is ready, let’s understand how to interpret it.

The columns represent the profit generated by each product and are arranged from the highest profit to the lowest profit. The cumulative percentage line starts with the top-performing product and continues increasing as each additional product is included.

Eventually, the line reaches 100%, representing the combined profit from all products. The point where the cumulative percentage line crosses the 80% reference line indicates the group of products generating approximately 80% of the total profit.

In this dataset, the top six products contribute around 80% of the total profit:

Cosmetics
Household
Office Supplies
Baby Food
Cereals
Clothes

The remaining six products contribute approximately 20%:

Vegetables
Meat
Snacks
Personal Care
Beverages
Fruits

This means that 6 out of 12 products, or 50% of the products, are generating around 80% of the total profit. That is not an exact 80/20 distribution, and that is perfectly fine.

The Pareto Principle is an observation and prioritization technique, not a rule that every dataset must follow exactly. The important insight is that the contribution is concentrated among a smaller group of products.

Making the Pareto Chart Dynamic with a Region Slicer

The real strength of this analysis becomes visible when we make the chart interactive. Add a Region slicer to the report so that users can analyze the Pareto distribution for different geographical selections.

For example, suppose we select:

Asia
Europe
North America

The chart will recalculate the profit and cumulative percentage based only on those selected regions.

Pareto Analysis for Asia, Europe, and North America

Dynamic Pareto Chart in Power BI

In this selection, Cosmetics and Household alone contribute close to 80% of the profit. Since these are 2 out of 12 products, approximately 17% of the products are contributing close to 80% of the profit for the selected regions.

Now consider another selection containing:

Australia and Oceania
Central America and the Caribbean
Middle East and North Africa
Sub-Saharan Africa

Pareto Analysis for the Remaining Regions

Dynamic Pareto Chart in Power BI

For these regions, the top three products, Cosmetics, Household, and Office Supplies, generate close to 80% of the profit. This is why dynamic Pareto Analysis can be so useful. The most important products at the overall business level may not be the same products that dominate within every region, customer segment, or market.

The DAX calculation using ALLSELECTED allows the chart to respond to the user’s selections while maintaining the required cumulative logic.

An Important Point About Products with Equal Profit

There is one scenario you should keep in mind.

The cumulative measure compares products based on their profit values. If two or more products have exactly the same profit, they may be included together in the cumulative total.

For many business datasets, this is acceptable. However, if you need every product to have a unique position, you may need to introduce a deterministic tie-breaking rule using product rank, product ID, or product name.

Where Else Can You Use This Analysis?

Once you understand the DAX pattern, you can apply the same technique to many other scenarios.

For example, you can identify:

Customers generating 80% of revenue.
Suppliers responsible for 80% of procurement spend.
Defect types causing 80% of quality issues.
Support categories generating 80% of service tickets.
Studies contributing 80% of clinical site costs.
Reports consuming 80% of Power BI or Fabric capacity.
Products responsible for 80% of returns.
Campaigns producing 80% of marketing conversions.

You only need to replace the product attribute and profit measure with the dimension and business metric required for your analysis.

Conclusion

Pareto charts provide a simple yet powerful way to identify the products, customers, causes, or activities making the greatest contribution to a business outcome.

In this tutorial, we created a dynamic Pareto chart in Power BI using DAX. We calculated cumulative profit, converted it into cumulative profit percentage, added an 80% reference line, and made the analysis responsive to Region slicer selections.

Most importantly, we saw that the 80/20 rule does not need to produce an exact 80% and 20% split. That is where Pareto Analysis becomes useful.

I hope this tutorial helped you understand not only how to build a Pareto chart in Power BI, but also how to interpret it correctly.

Have you used Pareto Analysis in one of your Power BI reports? If yes, what business problem were you trying to solve?

Do share your experience or questions in the comments below. I would love to hear from you.

Continue Learning:

If you found this tutorial useful, you may also enjoy:

Explore more tutorials on Power BI with Akshay.

If you find my blogs valuable and would like to support my work, you can
Buy Me a Coffee.

Power BI with Akshay

Search This Blog