## Pareto Charts in Tableau

“A **Pareto chart**, named after Vilfredo Pareto, is a type of chart that contains both bars and a line graph, where individual values are represented in descending order by bars, and the cumulative total is represented by the line.” —- from Wikipedia

Recently I had to create one of these and found that I had to look in a lot of places to get what I was looking for so I hope this will help you if you need to create one.

## To start off with you need to create a % of Total Sales field

I will use the default superstore data in Tableau so that you can try this yourself.

Double click on Sales

Double click on Customer Name

Then Sort the Customer name on the Columns shelf by SUM([Sales])

Make the Chart fit the width of your screen and you should have something like this.

Thats easy enough to get the first view of the bars but now we need to add in another line to show the cumulative total.

Whilst holding down the CTRL key left click on the SUM([Sales]) on the Rows shelf and drag it the side so that you have two measures.

On the first Sales pill Right click and pick a Quick Table Calculation and Running Total

Right click on the same pill again and click on Edit Table Calculation

Tick the box to Perform a Secondary Calculation

Select Percent of Total

Now click on the Customize button on the bottom left of the window, This will show what calculation is being done and you can also save this as a new Field.

Give it a name like % of Total Sales and click OK

You will come back to a smaller window with the table calculation for “% of Total Sales”

Click OK and you will see your first pill is not the SUM([Sales]) any more.

## Next we need to turn the Customer Name field from Text into a Calculation

Right click on the Customer Name pill and select Measure and then Count (Distinct)

This will change your view to show just one point on each measure

Right click on CNTD(Customer Name) and pick a Quick Table Calculation and Running Total

Right click on the same pill again and click on Edit Table Calculation

Tick the box to Perform a Secondary Calculation and select Percent of Total

Click on the Customize button and save the calculation as a new field “% of Customers”

Optional – You may wish to change the default number format for both % of Customers and % of Total Sales to be Zero decimal places but this is just a cosmetic point.

## Bringing it together

Drag from the Dimensions shelf a new Customer Name field and drop it onto the Level of details on the All Marks Card

Right Click and Sort this field by the Sum of Sales

Now Right click on the % of Customers and click on Edit Table Calculations and pick the Customer Name in the Compute using drop down and click Apply and OK

You should have something like this

Next Right click on the % of Total Sales pill and do the same

Make the Chart into a Dual Axis by either Right clicking on the Sum of Sales axis and selecting Dual axis or Drag and drop the axis to the right hand side of the % of Total Sales section

On the Marks card I like to set the % of Total Sales to a line and the Sum of Sales to an area so that you have something like this

And there you go a basic Pareto Chart.

## You want more?

Ok I do like to play with my Parameters 🙂 and they do make this more interactive.

Right click on the % of Total Sales axis and add a Reference line

Create a new Parameter called “Target % of Sales” with a current value of 0.8

Now to see where this crosses the % of Customers we need to create a calculated field

Right click on the % of Total Sales in the Measures shelf and select create a calculated field

Name the field Target Cross Point and use the following formula

If [% of Total Sales]<[Target % of Sales] Then [% of Customers] Else NULL END

Change the default number format to be a %

Then drag it onto the details marks card

Right Click on the % of Customers axis and add a reference line using the Target Cross Point

Set the Aggregation to be Maximum and the Label to Value

You will now have a second reference line that crosses at the point where the the target line meets the % of Total Sales curve

By changing the Target you will change the crossing point

## You want even more???

OK, only because I have the same question as you how to use this nice looking viz

To start with you need to duplicate the sheet you have just made (it just saves some time but you can do it manually)

Using the Show Me function select a Text Table

Drag the Target Cross Point measure field into the filter shelf

Click on the Special tab and select Non-null values

This will show you everyone that makes up the Target % of sales

If you set it to Null values then it will show you everyone after the Target % of Sales

I’ll set mine to 10% so you can see the values

Join these together in a dashboard and you should have something like this

[tableau url=”https://public.tableau.com/views/ParetoChartExample/ParetoExample?:embed=y&:display_count=yes&:showTabs=n” width=”800px” height=”800px”][/tableau]

Exactly what I need .. thank you!

Great job Chris, this has been useful. I had built various parts of this over the years but having it in 1 single place is terrific.

Great tipp. Thanks.

Do you know a way to show only the running sum of sales at the cross point under the chart? Moving Customer Names to the Detail shelf doesn’t really help.

To get the sum of the values in just the one table you need to reuse the Target Cross Point calculation and put in the values you are after or NULL. This will enable you to then use window calculations.

Start with duplicate one of the tables and then try and get only the sales up to the cross point.

“Running Sum up to Cross Point”

If [% of Total Sales] < [Target % of Sales] Then RUNNING_SUM(Sum([Sales])) Else NULL END

Then spread these across every row with a window max

“Window Max of Running Sum”

WINDOW_MAX ([Running Sum up to Cross Point])

Make sure both are addressing your Customer name and then you can drop customer name onto the details marks shelf.

Last step is a you want to remove all the extra details so other measures are removed and the below calculation takes only one mark and hides the rest.

IF FIRST()=0 THEN [Window Max of Running Sum] END

Then you can drop this calculation onto the filters shelf to remove all the Null marks and make sure it is addressing customer name again and you should be left with one value.

OMG! Thank you! I spent AGES trying to find a way to return a list of customers off of my Pareto Chart!

One question, when you try to use the intercept/cross-point in a title it outputs a range like 0%-10%. Do you know of a way to just use the 10% in a title? I’m attempting to write a SUPER simple interpretation of the chart and having trouble getting just that one intercept value.

Hi Laura,

To get this one value you can use the window max method on the Target Cross Point

WINDOW_MAX([Target Cross Point])

then add this to the details marks shelf and then you can add it onto the title from the drop down menu.

I hope that helps

Wondering how when a user is filtering a Paretto chart, how can i dynamically, in another worksheet using text have it automatically fill in the below sentence.

” 15%” of your “Billing” customers make up “80%” of your billings.

where 15% is where the # of customers meets the “80%” reference line.

The user could change to look at Credit customers and the %tages in the text would change. I do this alot with insights pages.

Hi Robert,

You could simply add this as a title to the table as per my answer above, use the WINDOW_MAX([Target Cross Point]) on the details and then edit the title with the test including the window max filed and also the parameter value to these change as you need them to. Or if you wanted it on a sheet and in the text shelf then I would use a duplicate of the table to start. Then taking in the window max of target cross point into the view and you should see the value the same on every row (if it is not then check it is addressing the customer name). You can remove the other measures at this time and add in the parameter value to teh text field and you should now see a grid of the same values repeated many times.

Last step is to get only one value so I would go with this calculation dropped onto the filters shelf and set to address customer name and Non-null values in the special section.

IF FIRST()=0 THEN [Window Max Target Cross Point] END

You can now click on the text label shelf and add in fixed text to go with your dynamic values.

I hope this helps.