Welcome to my tutorial on Pareto Charts. This tutorial will demonstrate step by step how to create a Pareto chart. As you follow along you can watch and listen to my demonstration in parts or scroll down to the bottom of the page and watch the full recording in one video.
Firstly, what is a Pareto chart?
A Pareto chart is a chart that has been developed to apply the Pareto Principle, named after Vilfredo Pareto. This principle is commonly known as the ‘80/20’ rule. The idea behind the ‘Pareto Principle’ is that around 80% of results are due to approximately 20% of the contributors.
For example: approximately 80% of ‘Sales’ are driven by only 20% of the products or only a small number of expense categories contribute around 80% of the total expenses.
This is an important concept as one can identify areas to focus on and areas that are underperforming.
The Pareto Chart displays the cumulative percentage of the results and the cumulative percentage of the contributors, allowing the user to visually and statistically see the driving factors of areas of focus.
You can follow along by downloading the dataset I used, or you can try to apply this to your own dataset.
Getting Started - Adding the dataset to Tableau
Alright, let’s dive in.
We will begin with loading the dataset into Tableau. Start by opening Tableau and connect to the data. It’s a Microsoft Excel .xlsx file, so you can select that option. You will be taken to the Data Source page where you should automatically have the table sheet open. It is a cleaned dataset, so you don’t need to make any changes here; just go ahead and click on sheet 1.
Step 1: Build the Base Pareto Chart
Let’s begin with the chart setup:
Right click and drag ‘product name’ to the columns shelf. This allows us to choose the aggregation on the ‘product name’, and we want that to be a count distinct (CNTD).
Next, we are going to drag ‘Sales’ to the rows shelf, then drag ‘product name’ -again- but to the marks card on detail.
Now, we are going to do a table calculation. We will first do it for ‘product name’, so we can click the little drop-down, and then we are going to say, ‘add table calculation’.
The calculation type is
- Going to be a ‘running total’,
- on ‘compute using’,
- choose ‘specific dimensions’,
- click ‘product name’,
- in ‘sort order’,
- we are going to click it to show the drop-down and change it to custom,
- change the two options to: ‘Sales’, and ‘sum’, and
- adjust the final option to ‘descending’.
Afterwards, we can make sure that ‘Secondary Calculation has been ticked, and, once again, we can choose ‘specific dimensions’ and adjusting the sort order to be the same as before—product name should already be ticked.
Now, we are going to do a table calculation for ‘Sum(Sales)’.
Click the little drop-down for sum of ‘Sales’. It’s going to be the same as earlier.
- We are going to do a running total,
- On ‘compute using’ choose ‘Specific dimensions’,
- Click ‘product name’,
- in ‘sort order’,
- Click it to show the drop-down and change it to custom,
- Change the two options to: ‘Sales’ and ‘sum’ and
- Adjust the final option to ‘descending’.
Then we can add a secondary calculation. It is going to be a ‘Percent of total’, with a ‘compute using’ of ‘specific dimensions’, and selecting ‘product name’. The sort order is going to be custom therefore, we can adjust these to ‘Sales’, ‘sum’ and ‘descending’.
So, that is the first step to making a Pareto chart.
Let’s dive into the next layer of a Pareto chart.
You might notice that the chart is currently set to automatic, as a result, it is displaying a messy arrangement of circles, but this can be easily solved.
On the marks card dropdown, change it to ‘lines’.
Now we’ll create a reference line.
- Right click of the X-axis.
- and add a ‘reference line’.
- Under ‘line’ and in the ‘value’ drop-down box, click on it.
- Choose ‘create a new parameter’.
- Rename the new parameter to ‘% of products’.
- Change the display format to a percentage.
- Change the format to one decimal place.
Furthermore, we want the label to display the percentage not the name of the value, so change that by clicking on the label box and change it to ‘value’ and click OK.
We are now going to right click on the Y-axis to create a reference line, just as before.
- Create a new parameter,
- Rename it to precent of ‘Sales’.
- Change the display format to percentage
- and reduce it to one decimal place
- Click OK.
- Change label to be ‘value’
- and then click OK.
The next part is to add worksheet actions so the reference lines can work.
- If we go to worksheet actions and say add an action and this is going to be a change ‘parameter action’
- Then we are going to name that ‘Update% of products’.
- The source sheet is your current sheet,
- the target parameter is ‘% of products’,
- and the source field is ‘% of Total Running Sum of CNTD(Product Name) along Product Name’
- and you want to run this action on select.
- Keep the current value.
- Click OK.
We are going create another parameter action.
- Go to add an action and select ‘change parameter’.
- rename it to Update % of ‘Sales’.
- again, the source sheet is fine.
- Change the target parameter to ‘% of Sales’.
- The source field is the ‘% of Total Running Sum of SUM(Sales)’.
- Run action on select.
- Aggregation to ‘sum’.
- Keep current value.
- Click OK.
- then Click OK again.
As a result, we can notice that, on our chart, the reference lines have updated.
But we are not done yet. We’re going to create a dynamic title, therefore, we can click on our title and say edit title.
Write ‘Top’ then click on ‘Insert’ and choose to insert our parameter for products and write, ‘ products make up’…. then insert ‘Sales’, and continue, ‘of all ‘Sales’’, and click OK.
At this point our chart is telling us that the Top 36.1% of products make up 69.6% of all ‘Sales’.
Now, we can do some final formatting. Let us change the view to ‘entire view’ and we can select on different points so see how the chart updates and make sure everything is working.
In Summary
That brings us now to our conclusion on creating a Pareto chart on Tableau. If you’d like to recap you can view the full video below.
I hope after following this tutorial you are able to apply these steps to your own visualisations so that the next time you come across a scenario where you want to investigate the driving factors or contributors to a situation you will be able to create your own Pareto chart.
