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.
I realize this is an old post, but its a very helpful technique to get the chart and very well put information so thanks for that.
I’m facing a small issue at the end while using the 2019.4 version of the software… the line and area graphs are disconnected once I apply them in the last step… is there anything I can do to fix that from your experience?
Hi Mousab, Thank you for your comments and I have not tried to reproduce this chart in the latest version yet but I will try and find some time and see if anything is different now and get back to you when I can. If you have already found a solution please let us all know it would be great to share it in case anyone else has the same issue.
Chris – thank you so much a Pareto chart is exactly what I needed to show how many sales our employees generate. I just have one issue I want to get your input on.
When I set the Target % of Sales to 100%, I expect the % of employees to also be 100%. I am seeing 73%. When I set the target over 100% then the % of employees equals 100%. I cannot figure out what’s wrong.