How to Create a Pareto Chart in Power BI

A Pareto chart is one of the most powerful tools for finding the "vital few" issues that cause most of your problems, helping you focus your energy where it counts. Based on the 80/20 rule, it visually separates the significant from the insignificant. This guide will walk you through, step-by-step, how to create a dynamic and insightful Pareto chart directly in Power BI.

What is a Pareto Chart and Why Should You Care?

The Pareto chart is named after Vilfredo Pareto, an economist who noticed that about 80% of the land in Italy was owned by 20% of the population. This concept, known as the Pareto Principle or the 80/20 rule, shows up everywhere in business:

• 80% of your sales likely come from 20% of your customers.
• 80% of customer support tickets are related to 20% of your product's issues.
• 80% of project delays are caused by 20% of the possible reasons.

A Pareto chart visualizes this principle to make it immediately obvious. It consists of two key components:

1. A bar chart: This displays a set of problems or causes, ordered in descending order from the highest frequency to the lowest.
2. A line graph: Overlaid on the bar chart, this line shows the cumulative percentage of the total.

The whole point is to help you figure out what to tackle first. By fixing the issues on the left side of the chart, you solve the majority of the problem with the least amount of effort.

Preparing Your Data

Before you jump into Power BI, your data needs to be structured properly. It’s pretty simple - all you need are two columns:

• A categories column: This contains the text descriptions of the problems, causes, or items you are measuring (e.g., "Wrong Size," "Late Delivery," "Software Bug").
• A values column: This contains the numeric count or frequency for each category (e.g., the number of times each complaint occurred).

For example, if you're analyzing customer complaints, your data in an Excel or Google Sheet might look like this:

Keeping your source data clean and simple like this will make the whole process much smoother.

Step-by-Step Guide: Building a Pareto Chart in Power BI

Power BI doesn't have a one-click "Pareto chart" button, but you can easily build one using a combination chart and a little bit of DAX (Data Analysis Expressions). Don't worry, we'll break down the formulas in a simple way.

Step 1: Load Your Data into Power BI

First, get your data into Power BI.

1. Open Power BI Desktop.
2. On the Home tab, click Get data.
3. Choose your data source (e.g., Excel workbook, Google Sheets).
4. Navigate to your file, select the correct table or sheet, and click Load.

You’ll now see your data fields in the Data pane on the right-hand side.

Step 2: Add a Line and Stacked Column Chart Visual

This combined chart type is the perfect foundation for our Pareto. In the Visualizations pane, find and click on the Line and stacked column chart icon to add it to your report canvas.

Step 3: A Small but Important DAX Measure for the Count

While you can just drag your raw 'Count' field into the chart, it's best practice to create a simple DAX measure first. This makes our later, more complex formulas easier to write and manage.

1. Right-click on your table name in the Data pane and select New measure.
2. This will open the formula bar. Enter the following formula and press Enter:

Total Complaints = SUM('Complaints'[Count])

Remember to replace 'Complaints' with your table name and [Count] with your values column name. Now you have a clean, reusable measure ready to go.

Step 4: Add Fields to Your Chart

Now, let's start building the visual:

• Drag your categories column (e.g., 'Complaint Type') to the Shared axis field.
• Drag your new [Total Complaints] measure to the Column values field.

At this point, you should see a simple column chart.

Step 5: Create the Cumulative Percentage Measures with DAX

This is where the magic happens. We need to calculate the cumulative percentage line. To make this easy to understand, we’ll build it in three stages: rank the complaints, calculate the cumulative total, and then calculate the percentage.

1. Rank the Complaint Types

First, we need to rank each complaint type from most frequent to least frequent. Create a new measure:

Complaint Rank = 
RANKX(
    ALLSELECTED('Complaints'[Complaint Type]),
    [Total Complaints],
    ,
    DESC,
    DENSE
)

Here's what this formula does:

• RANKX: This is the ranking function.
• ALLSELECTED('Complaints'[Complaint Type]): It tells the function to consider all complaint types currently visible in the chart, respecting any filters you may have applied to the page.
• [Total Complaints]: This is the value we are ranking by (the higher the count, the better the rank).
• DESC: It stands for descending, so the complaint with the highest count gets rank #1.

2. Calculate the Running Total

Next, we use our new [Complaint Rank] measure to calculate a running total, also known as a cumulative total. Create another new measure:

Cumulative Complaints =
CALCULATE(
    [Total Complaints],
    FILTER(
        ALLSELECTED('Complaints'[Complaint Type]),
        [Complaint Rank] <= MAX([Complaint Rank])
    )
)

Let's unpack it:

• CALCULATE([Total Complaints], ...): It re-calculates the initial sum, but with a special filter applied.
• FILTER(...): This creates a temporary table that includes only the rows where the rank is less than or equal to the current rank being evaluated. For example, when evaluating the third-ranked item, this calculation sums up items with rank 1, 2, and 3.

3. Calculate the Final Cumulative Percentage

Finally, we turn that running total into a percentage. Create your final DAX measure:

Cumulative % = 
DIVIDE(
    [Cumulative Complaints],
    CALCULATE([Total Complaints], ALLSELECTED('Complaints'[Complaint Type]))
)

This one is simple:

• DIVIDE(...): This function safely handles division to avoid errors if the denominator happens to be zero.
• It divides our [Cumulative Complaints] measure by the total number of complaints across all selected complaint types, giving us our running percentage.

Awesome job! The hard part is over.

Step 6: Finalize Your Pareto Chart

Now we just have to put the last piece in place and clean up the formatting.

1. Add the Cumulative % to the Chart

Drag your new [Cumulative %] measure into the Line values field in the Visualizations pane. You'll instantly see a line appear over your bars.

2. Sort the Chart Correctly

A Pareto chart is only useful if it's sorted from highest to lowest.

• Click the three dots (...) in the top-right corner of your chart visual.
• Navigate to Sort axis > Sort descending.
• Then, go back to Sort axis and make sure it's sorting by [Total Complaints], not by 'Complaint Type'.

3. Format the Visual

A little bit of formatting makes your chart much easier to read.

• Select your chart and go to the Format your visual pane (the paintbrush icon).
• Y-axis: In the Y-axis settings for the line chart (often called "Secondary Y-axis"), make sure the scale's Maximum is set to 1. This ensures your percentage line goes up to 100% and doesn't get cut off. You'll also want to format the values as a percentage here.
• Data Labels: Turn on Data labels to see the exact values on your bars and line points, making it easier to read.
• Colors and Titles: Adjust the colors of your bars and line graph for better contrast, and give your chart, X-axis, and Y-axes clear and descriptive titles.

Once you’ve done that, you’ll have a professional, fully functional Pareto chart!

How to Read Your New Pareto Chart

You did the work, and now you have a great visual. But what does it mean? Reading a Pareto chart is simple:

• Look at the bars: The bars on the left are your most frequent problems. These are your "vital few." The long tail of smaller bars on the right are the "trivial many."
• Trace the 80% line: Find the 80% mark on your secondary (right) Y-axis. Mentally or even by adding a constant line in the analytics pane, trace that across to where it intersects with your cumulative percentage line.
• Identify your focus areas: Look at the bars that fall to the left of that intersection point. In our example data, "Shipping Damage" and "Wrong Size Ordered" would be the two categories that contribute to nearly 80% of all issues.

Without much analysis, you've pinpointed exactly where to focus your quality improvement efforts for the biggest impact. Solving just those two problems will make most of your customer complaints disappear.

Final Thoughts

Creating a Pareto chart in Power BI transforms a flat list of data into a clear prioritization tool. By following the steps to build the visual and create the necessary DAX measures, you can apply the 80/20 rule to your business data and make more informed decisions about where to spend your time and resources.

We know this process inside Power BI involves several manual steps and a bit of a learning curve with DAX. That's why we created a tool to get you insights like this much faster. With Graphed, you could connect your data and just ask, “Show me a Pareto chart of customer complaints,” and get a real-time, interactive visual in seconds. We help you skip the complicated setup so you can get straight to the answers you need to grow your business.