Community #WorkoutWednesday challenge this week was set by Donna Coles and it was about Pareto chart with certain twists. Here, Requirement was to create Pareto chart to see what proportion of customers make up sales with an option of sliding parameter and few tooltips to make it user friendly along with ease of understanding.
Requirements:
- Dashboard 800 x 800.
- Selection of the Sales Percentage slider will adjust the highlighted point.
- Sales Percentage slider should increment in 10s from 10%-90%.
- % values displayed to 1 decimal point.
- The Sales Percentage mark highlighted should be the value that is at least the value selected in the slider (eg when 60% is selected, the mark highlighted is 60.1% as the previous mark is 59.9% which is less than the slider value).
- 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.
- Rebuild everything to be identical, matching titles, tooltips, chart formatting.
Data for the workout can be downloaded from here
Here is my output for the challenge (Click on gif for interactive version):
Here are the steps which I undertook to create this dashboard and complete the challenge
Step 1: Create the Parameter as per requirement to select Sales %
Step 2: Create calculated fields required to create the visualisation and calculations
My attempt was to create the dashboard with minimum steps. I created 6 calculated fields + 2 copies of calc fields
% of customers: On X-axis we need to show the running % for distinct customers
% of Sales: on Y-Axis we need to show running % for sales
Customer Point: This field is to show “% of customers” when “Sales %” is >= “% of Sales” field
Dot: Here we will use lookup function to find previous value of “Customer Point”. This field is to circle the position and highlight % of customers basis of % of sales (if 80% sales is selected then to show point at 49.9%)
Fixed Customers: This field to count the distinct number of customers
Sales Point: This field was more of after thought after re-looking at the requirements. This field we will show “% of sales” when “Sales %” is >= “% of Sales” field (if 80% selected then to show 80.1%)
Then, duplicate the “% of customers” and “% of Sales” Field for the purpose of tooltip and format this with 1 decimal point.
Step 3: Create the Sheet for the viz
Drag “% of Customers” and “Dot” field to column shelf then drag “% of sales” to row shelf
Select Customer Name and drag that field to details in Marks area then sort the field descending by sales
Select the graph type as “Area chart” for “% of customers” and “Circle” for “Dot” Field
Now, select “Dual Axis” on Dot field on column shelf then synchronize the axis and remove the “Measure Names” from color shelf
select “Grey” color for the area chart and “Black” color for circle Chart
For the reference lines on sales & customer, drag “Customer point” and “Sales Point” to Marks area
Right click on Y-axis and select add reference line, then select sales point from drop down and set as minimum
Similarly, Right click on X-axis and select add reference line, then select Customer point from drop down and set as minimum
Post this step, your viz is ready and will look like this:
Tooltip: drag “% of sales” , “% of customers”, “Fixed customers” into marks area for “Dot” field and additionally we will have distinct count of customer id on the basis customer name then we will modified the tooltip to see our requirement
Format the sheet and clean it up to meet the requirements and add the sheet into dashboard with few cosmetic changes.
And we are ready with final visualization as show above.
Thanks Donna Coles for this workout.
Happy Data Visualisation!!!!
Thanks for visiting this post. Please do let me know your feedback or if you have any questions about the blog do not hesitate to contact me on twitter (@Desaimithun)
Do subscribe to Tabvizexplorer.com to keep receive regular updates.