This is possibly my favorite thing to do create in Tableau. I really like these charts as they give a clear overview at first glance and then allow you to dig deeper into a category to get more info.
I created a drill down bar chart for this week’s makeover Monday (week 19 2020). I’m going to use this example to go through the steps on how to create this interactive chart. This week the data was from the World Happiness Report and had happiness scores for different countries.
There was a natural hierarchy in this data; as obviously individual Countries belong to a Region. Therefore, I wanted my bar chart to show the average happiness score for each Region with the ability to select a region and then for this to breakdown by Country.
First of all, I put the measure we are using on the columns, in this case this is Average Happiness Score. Then I place the highest level dimension to the rows and sort this descending. This creates a flat bar chart, in this case for each region.
Creating the Set
The next step is to create our set. To do this I’m going to right click on my region and select create –> set. Here I’m going to give this a name ‘Region Set’ and then select any of the regions (it doesn’t matter which one you select at this point).
Now we need to include this set in a calculation to tell tableau what to do when this set is selected. Create a calculation, I’m going to call this ‘COUNTRIES’ in capitals to distinguish between this field and the original countries field. My calculation is going to say if my region set it selected then return countries, if its not selected then blank. The formula should like this:
Placing this field on the rows next to my region should create something that looks like this:
As you can see we only have a breakdown by countries for Western Europe as that is what we selected in the set up of our region set. Now we need to add interactivity to the chart and to do this we will use set actions.
Creating the Set Action
To do this go to the worksheet tab and then actions. When the window pops up select ‘add action’ and then ‘change set actions’. Now you need to configure the set action:
- Select the source sheet, this is most likely the sheet you are currently working on.
- Choose whether you want to update the set when the action is hovered, selected or product a menu. I usually always choose select.
- Select your target set. In this case its my region set.
- Decide what you want to happen when the set is cleared.
- Keep set values: this means that the set that was previously selected will remain in the view when the set is cleared.
- Add all values to set: this means that everything will be added to the set. In our example all our regions will open up to display their member countries.
- Remove all values from set: this means that there will be no breakdowns shown when the set is deselected.
The set itself is a Boolean field which returns ‘In’ or ‘Out’ depending on whether the option is in the set or not. You can drag the set directly onto the colour mark to distinguish the breakdown between region and countries.
You will now have something that functions like the below chart:
It is important to note that as we have created this Set Action as a Worksheet Action that the configuration will have to be duplicated as a Dashboard Action when the chart is placed on a Dashboard!
For more on how to further format a drill-down chart see my next blog here.