Aug
19
2018

WorkoutWednesday S02 E33 -Pareto chart with a twist

Do you enjoy working with Tableau? Are you looking for an additional challenge? Workout Wednesday might be what you need. WorkoutWednesday a set of weekly challenges from  follow link Andy Kriebel and  where to buy provigil online Emma Whyte (for 2017) and  go site Rody Zakovich and  Luke Stanke. (For 2018) designed to test your knowledge of Tableau and help you improvise your development skills. The idea is to replicate the challenge that they pose as closely as possible. When you think you have it, leave a comment with a link to your visualization and post a pic on Twitter for others to enjoy.

2017 WorkoutWednesday Challenge: Link

2018 WorkoutWednesday Challenge: Link

Challenge

This weeks challenge is to build a Pareto chart to understand what proportion of customers make up sales, and is based on an actual Visualizations Donna delivered for a business requirement. This challenge will give you an opportunity to try a new chart type, but also provide a bit of extra thinking for those that are familiar with them.

Data for the workout can be downloaded from here

One of the most common reason for using a Pareto chart ,you can depict through visual means exactly the problems or problem causes that must be addressed. The intended audience then can glance at the chart and quickly see the most common problems, or the most common causes for problems. That makes it easier to communicate the different projects a company should undertake for quality improvement success.

What is Pareto chart? When is it used?

A Pareto diagram is a simple bar chart that ranks related measures in decreasing order of occurrence. The principle was developed by Vilfredo Pareto, an Italian economist and sociologist who conducted a study in Europe in the early 1900s on wealth and poverty. He found that wealth was concentrated in the hands of the few and poverty in the hands of the many. The principle is based on the unequal distribution of things in the universe. It is the law of the “significant few versus the trivial many.” The significant few things will generally make up 80% of the whole, while the trivial many will make up about 20%.

There are at least four instances when creating a Pareto chart is advisable:

  • To analyze the frequency of defects in a process
  • To look at causes in a process
  • To figure out what the most significant problem in a process is
  • To communicate data with others.

Hope we are good with the theoretical aspect and now we can deep dive into the practical aspect of the problem.

Step 1:

    1. Connect to the Sample – Superstore data source which you downloaded from Data.world Website.
    2. From the Dimensions area of the Data pane, drag Customer ID to Columns.
    3. From the Measures area of the Data pane, drag Sales to Rows.
    4. Click Customer ID on Columns and choose Sort.

In the Sort dialog box, do the following:

    1. Under Sort order, choose Descending.
    2. Under Sort by, choose Field.
    3. Leave all other values unchanged, including Sales as the selected field and Sum as the selected aggregation.
    4. Click OK to exit the Sort dialog box.

Customers are now sorted from highest make up sales to lowest.

Step 2:

  1. Click the  SUM(Sales) on Rows and choose Add Table Calculation.
  2. Add a primary table calculation to SUM(Sales) to present sales as a running total. Choose Running Totalas the Calculation Type.Do not close the Table Calculation dialog box.
  3. Add a secondary table calculation to present the data as a percent of the total. Click Add Secondary Calculation and choose Percent of Total as the Secondary Calculation Type.This is what the Table Calculation dialog box should look like at this point:

Step 3:

Now as per the requirement, we want the customer to appear in %. So we need to change the customer ID dimension to measures.

There are three ways we can get % of customers.

Note: Please make sure you put  CustomerID on “Level of Detail.” You want to do this to prepare for changing your X-axis to “Count Distinct”, which will eventually lead to measuring the percentage of customers. Having Customer name on your detail shelf will allow you to run table calculations on a dimension.

Process 1:

You can use index()/size() on columns and can compute using Customer ID.

Process 2:

You can write your own calculation

RUNNING_SUM(COUNTD([Customer ID])) / TOTAL(COUNTD([Customer ID]))

Process 3:

This is my favourite as tableau will generate the required calculation in just two clicks.

Step 4:

Select the desired Pareto colour based on your choice. I am using #c0c0c0 with 60 %  opacity. At this moment your worksheet will look like the below image.

Step 5:

Now its time to create a Sales Percentage slider with an increment of  10s from 10%-90%.

 

Step 6:

As per the requirement, we need to create a highlighted point which will adjust based on the selection of the Sales Percentage slider.

Logic Involved :

When we add Parameter (Sales %) as a reference line, the generated line will touch the Pareto chart. Now our task is to capture the Intersecting point.

The easiest way is to find the in-between mark where the Current % Sales(Table Calculation) which goes over the Sales% (Parameter %) and previous mark which is less than Sales% (Parameter %).

 

 

 

 

 

 

Step 7:

Now we need one more calculation for X-axis Reference line which will change based on the Slider Selection.

 

 

 

 

Drag Dot calculated field to Rows next to Sales. Right click on “Dot” and choose Dual Axis. You will notice that now that you have the dual axis, the two axes have different scales. At this point, you need to decide whether or not the two axes should be synchronized. In this case, we will synchronise the axis. Now your worksheet will look like the below image.

 

 

Now we are almost done.

Wait a minute……….

We have two more requirement related to tooltips.

  • The tooltips on the curve differ from that on the highlighted mark.
  • The tooltip on the highlighted mark should also indicate the actual number of customers at that point, as well as the total number of customers.

Now we need a few more calculations for our X axis Reference line and Tooltips respectively. These calculations are easy to understand. So I will not go into detail. Please refer to the below image.

 

 

 

 

 

 

 

 

 

 

once these calculations are up and running, add that in tooltip section. Make sure you should select the correct Axis( Sales axis /Dot axis) for tooltip based on the requirement. You can add something like this.

 

Add a proper heading say

WHAT CUSTOMERS MAKE-UP XX% OF SALES?” and put this sheet on the dashboard. Your Final dashboard will look like the below image.

 

Related Posts

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

3 Comments+ Add Comment

  • Hello Mr. Rajeev Pandey,

    I tried this technique, but i am not getting exactly same view of test reference shape chart and also second reference is missing. could you please suggest where i went wrong.

    Thanks,
    rupasa

    • Can you please send me the copy of your work either via email or LinkedIn so that I can have a look

  • Great post!
    But do you think it’s possible to color the area to the left of the % of Customer Name to the left of the reference line? I tried it but couldn’t achieve anything that would even remotely look like what I hoped for…

Leave a comment

You must be logged into post a comment.