Performing Pareto Analysis using Tableau and Microsoft Excel

Pareto Analysis also called as 80/20 rule, is a decision-making technique which states that 80 % effects of things happen in the system, arises from 20 % causes. Consider below two examples to understand better:

• 80 % of your profit came from 20 per cent of Products you sold
• 80% of issues in your website/business are caused by 20% cause

In general, it explains top factors (20%) that can be responsible for big problems (80%). You can perform Pareto Analysis using Tableau and Microsoft Excel. Pareto Chart is considered one of the important tools of quality control as it’s easy to see the most common problems or issues that are affecting your business. The dataset that we are going to use here contains a website error along with the counts (number of times those issues occur). You can download it from here.

Pareto Analysis using Tableau

Steps to perform Pareto Analysis in Tableau:

• First, connect to the source or file which contains the data one which you want to perform Pareto Analysis. For this post, we are using a .xlsx file which you can download from here.
• Once you connected successfully and all your excel file has been imported in Tableau, click on the new worksheet option which you can find at the left side bottom
• Now you can see measures and dimensions in the left pane of the window
• For this post, we will be going to use issues which you can find in the dimensions which have all the issues that are generated in the website and another is count which shows the number of the times the issue is generated
• Drag Issues on the column shelf and Count on the Row shelf. Make sure to arrange counts in descending order.
• Now right click on the Sum(Count) and choose quick table calculation and then Running Total
• Now again right click on the Sum(Count) and choose to edit quick table calculation. A window pop up which shows Running total. Click on compute using option and make sure Issues has been selected. Now we want to show issues in percentage so we choose to add a secondary calculation option.
• Then a window extends to the right side showing the options for secondary calculation. Here we have to choose the per cent of the total so that all the issues have been arranged based on how much they contribute to the total. Now make sure y-axis is showing % of Total running
• Now we have to do the same action with the Column shelf which has issues
• First, click on an issue in column shelf and drag and drop to the details on the Marks card. Then right-click on the issue in column shelf and go to measure and select count distinct
• Then again right click and select quick table calculation and choose running total.
• Again same way as you have done above for Row shelf, right click and choose edit table calculation and in Primary calculation type, under Specific dimensions issues have been selected. If not then select it and also choose to add secondary calculation and select Percent of total option and then close the window
• Now on the marks card instead of Automatic choose Line
• For a chart to be a Pareto Chart, there should be a line as well as a bar chart.
• To add a bar chart drag a Number(count) of Issues from measures to Row shelf. Then right click on it and choose Dual Axis. Now, change the mark type for Second Number(count) to a bar. And Your chart is ready as you can see below clearly showing that First three issues 80% of your problem on which you focus first to sort them out.

 

Pareto Analysis using Microsoft Excel 

Now let’s see how we can perform Pareto Analysis in Microsoft Excel. Below are the steps to perform Pareto Analysis in Excel :

• Create a vertical bar chart with problems/issues/Products on the x-axis and count (number of occurrences) on the y-axis
• Arrange the bar chart in descending order of a maximum number of counts to the lowest count
• Now calculate the cumulative count percentage for each cause in descending order for each cause in descending order. Percentage calculation: {Individual Cause Count} / {Total Causes Count}*100
• Finally, Select the data and go to insert tab and choose Insert Combo Chart from Chart types in the ribbon. And then choose to create a custom combo chart. There you will see two different option to plot charts for your Counts as well as Cumulative count. For Count choose the Clustered column and for Cumulative count choose line chart and click okay and your chart is ready.

 

 

hope you like our post. If you have any issue in performing Pareto Analysis, Feel free to comment it below. I am happy to help you in sorting out the issue. If you like our post please share it.