How to Make a Pareto Chart in Power BI

A Pareto chart is one of the most powerful tools in any analyst's toolkit, built on the simple but profound 80/20 principle: roughly 80% of effects come from 20% of the causes. Identifying which website bugs cause the most support tickets, which products drive the bulk of your revenue, or which marketing channels deliver the most qualified leads is the first step to making smarter decisions. This guide will walk you through creating a dynamic Pareto chart in Power BI, step-by-step, including the DAX formula you’ll need to make it work.

What is a Pareto Chart, Exactly?

At its core, a Pareto chart is a combination of two things: a column chart and a line graph, plotted on the same visual. Think of it this way:

• The Column Chart: This part shows individual values for different categories. We sort these values in descending order, from the largest cause to the smallest. For example, the columns could represent the number of customer complaints for different categories like "Slow Shipping," "Damaged Product," or "Billing Error."

• The Line Graph: This line represents the cumulative percentage. As you move from left to right along the x-axis, the line shows the running total of the percentages for each column. The line always starts at the percentage of the first bar and ends at 100%.

The magic happens where these two visuals intersect. You can quickly see which "vital few" columns contribute up to 80% of the cumulative total, allowing you to separate them from the "trivial many." It immediately focuses your attention on what matters most.

Before You Begin: Prepping Your Data

Like any good analysis, success starts with clean, well-structured data. Thankfully, for a Pareto chart, the requirements are straightforward. Your dataset just needs two essential columns:

1. A Category Column: This contains the labels for the different causes you want to measure. It could be product names, marketing channels, support ticket types, website error codes, etc.

2. A Numeric Value Column: This is the frequency or magnitude of each category. This could be sales totals, complaint counts, user session numbers, or manufacturing defect counts.

Here’s a simple example of a dataset ready for a Pareto chart, tracking sources of website traffic:

With data like this, we can easily build a chart to see which channels are driving the vast majority of our website sessions.

Building Your Pareto Chart in Power BI: A Step-by-Step Guide

Once your data is loaded into Power BI, creating the chart involves a few key steps, from choosing the right visual to writing a simple DAX measure.

Step 1: Choose the Line and Stacked Column Chart

In the Visualizations pane on the right side of your Power BI desktop, select the "Line and stacked column chart" visual. It’s the visual that blends both chart types into one. Click it to add it to your report canvas.

Step 2: Add Your Data to the Visual

Now, let’s populate the chart with our data. Using our website traffic example:

• Drag your categorical column (Channel_Source) into the Shared axis field.

• Drag your numeric column (Sessions) into the Column values field.

At this point, you'll see a basic column chart. Power BI will probably sort it alphabetically by the channel source, but we need it sorted in descending order of sessions. To fix this, click the three dots (More options) at the top of your visual, select Sort axis, choose your numeric value (Sessions), and then select Sort descending. Now your bars will be arranged from largest to smallest.

Step 3: Create the Cumulative Percentage Measure with DAX

This is where the real power comes in. The line graph needs a cumulative percentage, which isn’t something in our source data. We need to calculate it using DAX (Data Analysis Expressions), Power BI's formula language. Don't worry, it's not as intimidating as it sounds.

Right-click on your table in the Fields pane and select New measure. A formula bar will appear at the top. Enter the following DAX formula:

Let's briefly break down what this formula is doing so you're not just copying and pasting blindly:

• VAR CurrentChannelValue: This variable simply calculates the sum of sessions for the current category in the chart (e.g., for the "Organic Search" bar).

• VAR AllChannelsValue: This calculates the total number of sessions across all channels, removing any filters so we have a grand total to use for our percentage calculation.

• RETURN DIVIDE(...): This is the main part.

  • The numerator uses CALCULATE and FILTER to add up the sessions for all channels that have a session count greater than or equal to the current channel's value. This is how we get a cumulative, running total.

  • It then divides that running total by the total value of all channels (AllChannelsValue) to get our cumulative percentage.

Hit enter once you've input the formula. You'll now see your brand new Cumulative Percentage measure in your Fields list, indicated by a small calculator icon.

Note: Make sure to replace 'Traffic Data'[Sessions] and 'Traffic Data'[Channel_Source] with your table and column names.

Step 4: Add the Cumulative Percentage to the Visual

Drag your new Cumulative Percentage measure into the Line values field well of your visual. A line will appear across your chart. Almost there!

Step 5: Format the Chart for Clarity

The chart is functional now, but a little formatting will make it much easier to read.

• Format the Percentage Axis: Your percentage is probably showing up as a decimal (e.g., 0.85 instead of 85%). Click on your Cumulative Percentage measure in the Fields pane. A "Measure tools" tab will appear at the top. In this tab, change the format from "General" to "Percentage" and set the decimal places to 0.

• Dual Y-Axes: By default, the line may look flat because it's being plotted on the same scale as your large column values. In the Format visual section of the Visualizations pane, go to Y-axis and turn on the Secondary Y-axis toggle. Adjust the range for this axis to go from 0 to 1 (representing 0% to 100%).

• Add Data Labels: Go to the Data labels section under formatting options to turn on labels for both the columns and the line, so you can see exact values without hovering.

• Give It a Title: Double-click the chart title to rename it to something descriptive, like "Pareto Analysis of Website Traffic Sources."

How to Read and Use Your New Chart

Now that your chart is built, find the point on the line that corresponds to 80% on the secondary Y-axis. The columns to the left of that point are your "vital few" - the 20% of sources causing 80% of the effect. In our example, you'd quickly see that Organic Search and Paid Search alone likely contribute to around 80% of all website sessions.

This insight immediately guides your strategy. Do you double down on what’s working? Do you investigate why other channels are underperforming? The chart doesn’t give you the answer, but it tells you exactly where to start asking the right questions.

Use Power BI's slicers to make your Pareto chart even more interactive. Add a slicer for a date range to see how the 80/20 rule for your traffic sources has changed over time, or filter by user country to see if a different channel is dominant in an emerging market.

Final Thoughts

Creating a Pareto chart in Power BI helps you cut through the noise and visually pinpoint what truly drives your business outcomes. By combining a sorted column chart with a cumulative percentage line created by a DAX measure, you can easily apply the 80/20 principle to focus your energy where it will make the biggest impact.

While Power BI is an incredibly powerful tool, mastering tasks like writing DAX measures and configuring visuals requires a significant time investment. We created Graphed because we believe your data shouldn't be locked behind a steep learning curve. Instead of navigating menus and writing formulas, you can just ask in plain English, "Show me a Pareto chart of support tickets by reason code." Our AI-powered analyst connects to your data sources, handles the technical work for you, and builds a live, interactive dashboard in seconds, not hours.