How to Make a Pareto Chart in Power BI with AI

A Pareto chart is one of the most powerful tools in any data analyst's toolbox, quickly showing you the "vital few" causes behind the "trivial many" problems. Based on the 80/20 rule, it helps you find the 20% of effort that will drive 80% of your results. This guide will walk you through creating a Pareto chart in Power BI, first using the traditional DAX method and then showing you a much faster way using Power BI's built-in AI features.

What Is a Pareto Chart? (And Why You Should Care)

The Pareto principle, or the 80/20 rule, states that for many events, roughly 80% of the effects come from 20% of the causes. It shows up everywhere once you look for it:

• 80% of a company's sales come from 20% of its customers.

• 80% of software bugs are caused by 20% of the identified coding errors.

• 80% of website traffic comes from 20% of its pages.

A Pareto chart visualizes this principle beautifully. It’s a combination of a bar chart and a line chart.

• The bars represent individual values (like complaint types or sales per product) and are ordered from highest to lowest.

• The line represents the cumulative percentage, showing how each category adds to the total.

The whole point is to prioritize. Instead of getting overwhelmed by a long list of problems, a Pareto chart instantly highlights the biggest contributors, helping you focus your resources where they’ll make the most impact.

Step 1: Get Your Data Ready for Analysis

Before you even open Power BI, you need data that's structured for this kind of analysis. It doesn't have to be complicated. At a minimum, you just need two columns:

1. A Category Column: The items you want to measure (e.g., Product Name, Reason for Return, Ad Campaign).

2. A Value Column: The numerical frequency or value for each category (e.g., Total Sales, Number of Returns, Cost).

Here’s a simple example dataset of customer support ticket reasons:

After importing your data into Power BI, it's a good practice to use the Power Query Editor to clean it up. Make sure your category column is formatted as Text and your value column is a Whole Number or Decimal Number. Removing any blanks or typos now will save you a headache later.

Method 1: Building a Pareto Chart Manually in Power BI (with DAX)

Understanding how to build a Pareto chart manually is a great way to learn the mechanics of Power BI and its formula language, DAX. It gives you maximum control, but it does require a few more steps.

Step 1: Create the Initial Combo Chart

First, drag a Line and stacked column chart visual onto your Power BI canvas. It's the perfect visual for a Pareto analysis.

• Drag your category column (Ticket Reason in our example) to the Shared axis (or X-axis) field.

• Drag your value column (Number of Tickets) to the Column y-axis field.

Now, click the three dots (...) at the top right of your visual, hover over Sort axis, and select your value field and Sort descending. This arranges the bars from highest to lowest, which is a requirement for a Pareto chart.

Step 2: Write the DAX Measures

This is where the magic happens. We need to create three simple DAX formulas, called measures, to calculate the cumulative total and percentage.

Right-click on your table in the 'Data' pane and select “New measure.”

Measure 1: Base Value CountThis is simply a sum of our values. It's good practice to create an explicit measure for this rather than using the default sum.

Measure 2: Cumulative CountThis measure calculates the running total of tickets as it moves down the list of sorted categories. It might look complex, but it essentially tells Power BI to sum the ticket count for all reasons that have a greater or equal count than the current reason.

Measure 3: Cumulative PercentageFinally, we calculate the cumulative percentage. This DAX formula divides the running cumulative total by the grand total of all tickets.

After creating this last measure, select it from the Data pane, and in the 'Measure tools' tab at the top, change the format to Percentage.

Step 3: Finalize Your Visual

Now you have everything you need. Drag your Cumulative Percentage measure into the Line y-axis field of your combo chart visual. You’ll see a line appear over your bars, tracking the cumulative contribution of each reason.

You’ve just built a Pareto chart! For extra clarity, you can format the visual by adding data labels, adjusting the titles, and maybe even adding a constant line at the 80% mark on the line axis to make the focus area stand out.

Method 2: The AI-Powered Way Using Quick Measures

Writing DAX can be intimidating, especially when you're just starting out. Thankfully, Power BI has an AI-assisted feature called Quick measures that writes the DAX for you based on simple inputs. This drastically cuts down the time and complexity.

Step 1: Set Up the Same Initial Chart

Just like in the manual method, start with a Line and stacked column chart. Add your category to the shared axis and your value to the column y-axis. Sort it in descending order.

Step 2: Create a Running Total with Quick Measures

Instead of writing our cumulative measure from scratch, we'll let AI handle it.

In the Home tab of the ribbon, click on Quick measure. A new window will appear.

1. Under Calculation, scroll down and select Running total.

2. Under Fields on the right, drag your value column (Number of Tickets) to the Base value box.

3. Drag your category column (Ticket Reason) to the Field box.

4. Make sure the Direction is set to Descending.

Click "Add." Just like that, Power BI has generated a long, complex DAX formula for you and added it as a new measure to your table. You can rename it something simple like AI Cumulative Count.

Step 3: Create the Cumulative Percentage Measure

We still need to calculate the final percentage, but now it's incredibly simple because the AI did the hard part. Create a new measure with this straightforward DAX formula:

As before, select this measure and format it as a Percentage from the ribbon.

Step 4: Add the AI Measure to Your Chart

Drag the new AI Cumulative Percentage measure to the Line y-axis of your chart. Voila! You have the exact same Pareto chart as the manual method, but in a fraction of the time and without deciphering complex DAX logic. You've successfully used Power BI's AI capabilities to simplify your analysis.

Reading the Tea Leaves: How to Interpret Your Pareto Chart

You've built the chart. Now what? Interpreting it is where the real value lies.

• Look for the bend: The cumulative percentage line will start off steep and then gradually flatten out. The "elbow" or "bend" is the sweet spot. The bars before this bend are your "vital few" — the items you should focus on.

• Find the 80% mark: Trace the 80% line from the right axis over to your cumulative line, then look down at the categories on the left. In our support ticket example, you'd see that "Login Issues" and "Slow Performance" together account for almost 80% of all tickets.

• Turn insight into action: The conclusion is clear. Instead of telling your engineering team to fix every single bug, you can give them a focused mission: "Let's pour our resources into solving login problems and improving server performance. That will eliminate nearly 80% of our support workload." This is a data-driven decision that leads to efficient problem-solving.

Final Thoughts

Creating a Pareto chart in Power BI is a fundamental skill that transforms raw data into a clear roadmap for action. Whether you manually write the DAX to understand the underlying logic or leverage the AI-powered Quick measures feature to get there faster, the result is the same: a focused view of what truly matters to your business.

This process of connecting data, writing formulas, and configuring visuals is powerful, but it's often more work than is needed for routine analysis. That's precisely why our team built Graphed. We believe you should get insights without becoming a part-time BI developer. With our tool, you connect your data sources once and simply ask for what you need in plain English - like, "Build a Pareto chart of support tickets by reason for last quarter." We handle all the background steps automatically and instantly generate live, interactive dashboards so you can get answers in seconds, not hours.