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 Andy Kriebel and Emma Whyte (for 2017) and 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
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.
- Connect to the Sample – Superstore data source which you downloaded from Data.world Website.
- From the Dimensions area of the Data pane, drag Customer ID to Columns.
- From the Measures area of the Data pane, drag Sales to Rows.
- Click Customer ID on Columns and choose Sort.
In the Sort dialog box, do the following:
- Under Sort order, choose Descending.
- Under Sort by, choose Field.
- Leave all other values unchanged, including Sales as the selected field and Sum as the selected aggregation.
- Click OK to exit the Sort dialog box.
Customers are now sorted from highest make up sales to lowest.
- Click the SUM(Sales) on Rows and choose Add Table Calculation.
- 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.
- 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:
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.
You can use index()/size() on columns and can compute using Customer ID.
You can write your own calculation
RUNNING_SUM(COUNTD([Customer ID])) / TOTAL(COUNTD([Customer ID]))
This is my favourite as tableau will generate the required calculation in just two clicks.
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.
Now its time to create a Sales Percentage slider with an increment of 10s from 10%-90%.
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.
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.