Today I want to talk about a very practical Workout Wednesday task. It is Week 48 2019, “Building Bar Charts that automatically combines small totals”. Before I deep dive into this specific task, I want to briefly go over Workout Wednesdays. Workout Wednesdays are weekly challenges to recreate data visualisations. They are compiled by anyone from the community, with the hopes of enriching and supporting each other in the data visualisation space. These challenges are a fun and a quick way to get more hands-on experience for Tableau. I enjoyed this weeks in particular as I can see a lot of practical use cases for day to day data visualizations.

 

The Challenge

The task for this challenge is to reproduce a bar chart that allows states to be classified as “All Other States” dynamically. What that means is, once you change the “Group States Contributing Less Than” parameter, states that do not meet the benchmark will be grouped together. I think this is a great case that shows another way for user interaction beyond “Top 10” Filtering, because now it allows the user to choose the benchmarks for comparison. What I personally like about this challenge is the mixture of different components needed, which are:
-LODs (Level of Detail)
-Collapsible Bar Charts (Grouping “Other States”)
-Ordering/Sorting

LODs (Level of Detail)

Level of Detail expressions (also known as LOD expressions) allow you to compute values at various levels. You can look at data at an overall level, data within groups and data across groups. Ultimately you have control on the level of granularity that you wish to compute certain calculations. There are three types of LOD expressions, fixed, include and exclude. LODs is a very complicated topic, but I like to simply the three expressions as such
-Fixed = Will look at data values at an overall level
-Include = Will look at data values at the most detailed level (Data within Group)
-Exclude = Will look at data values across the group

To start off this challenge, we will be using Fixed expression against Country/Region and Sales.
This will then allow us to get a % share for each respective state.
About now we should have something that looks like the below

Now we will need to create a parameter that will capture the states that fall below the benchmark. To do that, we create a floating parameter.

After that, we can now create a State Set that will categorise the States accordingly.

To group the states that fall below the benchmark, we will need to create the below set.

Putting that altogether respectively we should have the below.

If you take out State from the Row pane, all we need to do is to sort the charts.

As “All Other States” accumulate to be the group that has the highest % of Sales, it will always be either the top or bottom if it is sorted ascending or descending. To counter this, we can create a new field that will suppress “All Other States”.

We can now sort “All Other States” by the Sort States field we just created.

And here we have finalised the key elements of this challenge. The remaining components will be formatting the chart accordingly, from title, font, axis and chart labels.

The three key takeaways for this challenge
1. An example of Fixed LODs being used. To get the state percentage share of sales, you will need to fixate the total sum of sales at the Country Level.
2.Create another State Set. By creating another state set, this will allow states to be dynamically included or excluded from “All Other States”
3.Create a different Sort Field.

Anthony Wong
Author: Anthony Wong