Dynamic Sorting for Barbell Chart – Part 1 [Tutorial]

Barbell chart is an effective chart to compare two values, may it be a year-to-year comparison or an average and median price comparison. However, it comes with a limitation. With the default sorting Tableau has, the user cannot know:

 

1) The category with the highest value for Comparison A 

e.g. Which state has the highest current year sales?

2) The category with the highest value for Comparison B 

e.g. Which state has the highest previous year sales?

3) The category with the highest difference between the two

e.g. Which state has the most substantial growth in sales from the previous year?

 

This is where the ability to dynamically select how the barbell chart can be sorted is useful. I made this for a client project and it can potentially be used in other cases too.

This writing is part of a 3 blog posts series that will cover dynamic sorting for barbell charts in 3 different scenarios (with the good ol’ Superstore dataset as an example):

 

1) Comparison between the average and median sales for all states (row-level)

2) Comparison between the current year and previous year for all states (column level) – will provide the link here once completed 

3) Comparison between two time periods for all states which the user can select on their own – will provide the link here once completed 

4) And a separate bonus blog post on how to make a prettier user interface all done in Tableau – will provide the link here once completed 

 

Today’s aim

There are options to filter based on ‘Median Sales’, ‘Average Sales’ and ‘Difference’. Another option is to sort in ascending or descending order. 

But before you start reading the tutorial blog post, feel free to view it on Tableau Public here and try to reverse engineer it first. You can always refer back here if you’re stuck.

 

1. Create parameters 

We will create 2 parameters: 

1) Sort by (Sales) – for the user to select what the sorting is based on

Figure 1: In Parameter, select ‘String’ as data type under properties and type in all options under ‘List of values’

 

For this exercise, we’ll have 3 options for sorting: ‘Average Sales’, ‘Median Sales’ and ‘Difference’.

 

2) Sort Asc or Dsc – for the user to select whether to sort in ascending or descending order

Figure 2: In Parameter, select ‘String’ as Data Type under properties and type in the sorting order options under ‘List of values’

 

Click on the right end of the parameters in the pane that show an upside-down triangle and click ‘Show Parameter’ so you can change the values just like how you play around with filters.

 

Figure 3: Drop-down window that enables to show parameter

 

Figure 4: Parameter options appear at the same position as the legend

 

 

2. Create calculated fields for the metrics used for sorting

 

1)  [1. CALC_Difference] – the difference between ‘Average Sales’ and ‘Median Sales’

AVG([Sales])-MEDIAN([Sales])

2)  [1. CALC_AvgSales_DSC] – the average sales when sorted in descending

– AVG([Sales])

Add a minus sign in front of average sales so that when it’s sorted in descending, the larger real average sales value will appear at the top because a bigger negative number is smaller.

3) [1. CALC_MedianSales_DSC] – similar to above but for ‘median sales’

– MEDIAN([Sales])

4) [1. CALC_Difference_DSC] – similar to above but for ‘difference’ 

-([1. CALC_Difference])

 

3. Create a calculated field to be used on ‘Sort’

Almost reaching the last step, now create a calculated field that returns the value of how the user wants the chart to be sorted, depending on the selected values from the 2 parameters we created, [Sort Asc or Dsc] and [Sort by (Sales)]: 

 

[1. CALC_Sort by_AscDsc]

CASE [Sort Asc or Dsc]

WHEN ‘Ascending’ then

CASE [Sort by (Sales)]
WHEN ‘Average Sales’ then AVG([Sales])
WHEN ‘Median Sales’ then MEDIAN([Sales])
WHEN ‘Difference’ then [1. CALC_Difference]
END

WHEN ‘Descending’ then

CASE [Sort by (Sales)]
WHEN ‘Average Sales’ then [1. CALC_AvgSales_DSC ]
WHEN ‘Median Sales’ then [1. CALC_MedianSales_DSC]
WHEN ‘Difference’ then [1. CALC_Difference_DSC]
END

END

 

An if-condition statement also works here but a case-statement is handier when there are many conditions. In this calculated field, there’s a case statement inside a case statement. The outside case statement is based on the values from the parameter ‘Sort Asc or Dsc’. This parameter has fewer options compared to ‘Sort by (Sales)’ so making it the outside statement will help minimise the typing we’ll need to do. 

How the case statements work is when the user selects ‘Ascending’ then depending on the option that they choose as the metric to sort by on, it will return the values for that metric, e.g. AVG([Sales]). When the user selects ‘Descending’ then it will return the negative values from the calculated fields we created previously.

 

4.  Using the calculated field to sort the states on the barbell chart

 

Click on the right end of the ‘State’ pill that will show an upside-down triangle. Then select ‘Sort’ from the dropdown menu as follows:

 

Figure 5: Drop-down window appears and has the option to ‘Sort’

 

A pop-up window will appear:

Figure 6: Pop-up window showing how the field ‘State’ is to be sorted

 

Select to sort based on ‘field’ and choose the calculated field we created [1. CALC_Sort by_AscDsc]. 

 

5. A bit of formatting

Formatting the tooltip may seem like a waste of time, but tooltips can pack a lot of punch despite their size. Without any formatting, when the user hovers over the green dot there will be the Median Sales value and hovering over the blue dot will show the Average Sales. This is not ideal as we cannot compare these numerical values directly. 

We’ll make the tooltip show the following information regardless of whether the user hovers over the green or blue dot. 

1) Create calculated fields for ‘Average Sales’ and ‘Median Sales’ 

2) Drag these calculated fields to the tooltip in ‘Marks’ under ‘All’

3) Format the tooltip to include both values and the difference – edit the font size too, I found the default size to be small and difficult to read

 

Figure 7: Formatted tooltip

 

And that’s it! Dynamic sorting on barbell chart comparing Average Sales and Median Sales is completed. For the next post, I’ll cover dynamic sorting which compares the Current Year’s Sales and the Previous Year’s Sales. 

If you have any questions, feel free to reach out to me on Twitter or LinkedIn.

Cheers,

Jo