How to build small multiple gauge chart in Tableau
In my previous blog post, I did a walkthrough of my data prep process for the Iron Viz competition. In this post, I will dive into the steps I took to build the small multiple gauge chart.
As I mentioned in my previous post, I tweaked Ken Flerlage’s version of this chart to be able to implement this view on my dashboard.
Why I Chose a Small Multiple Gauge Chart in Tableau
I chose to combine the small multiple and the gauge chart because it is a great way to show a comparison of information in a given category.
In my visualization, I used the small multiple gauge chart to display details about the calorie levels within each food group selected.
I could have used a simple bar chart to display this information as well but there are so many labels to display at the bottom of the bars such that it doesn’t make the chart easy to read.
Since the small multiple gauge chart is not natively built into Tableau, it requires a little bit of work to make it visually appealing and easy to read.
How to Build It
The first challenge I encountered in creating the gauge chart was implementing a cross-join. For this chart type to work, I needed to join the food calorie list which contains 165 rows of data with a secondary data source that contained 5 rows of data to generate 825 rows.
A name for this type of result is known as the Cartesian product or cross-join. You can find out more about Cartesian products here.
To achieve this kind of join, you need to set a 1=1 join clause in Tableau. I used Tableau prep to achieve this. In Prep, I used the Connections tab to bring in both data sets above then I used the ‘Add Step’ option to create a clean step.
Now, since there is no relationship between the two tables, there was the need for dummy fields that would link them together.
I created these dummy fields in both data sets by simply clicking on the create calculated field option and typing in ‘1’ as shown below. To create this type of join, you have to make sure this is created in both data sets.
After creating these dummy fields, I named both of them ‘join’. I then used the Add Join option to create an inner join.
Tableau doesn’t always create the join automatically so you might want to click on the ‘ Join step’ to see if the join is created using the dummy fields you created in both data sets. This method creates the 1=1 cross-join.
After this join was created, I added an ‘Input step’ to check the number of rows in the join result.
This can be found at the top left-hand side of the profile pane in Tableau prep. Since the result produced is 825 (i.e. 165 x 5 rows), the join is working as expected.
Alternatively, you can preview results in Tableau Desktop by simply right-clicking the input step and selecting ‘Preview in Tableau Desktop’. After confirming my results, I added an ‘Output Step’ and created a Tableau extract.
All the calculations for the gauge chart can be found on Tableau Zen master Ken Flerlage’s website. In my tweak, the number 600 in the calculation represents the entire size of the semi-circle (i.e. total calories) and 300 represents half of that. Below are the tweaks I made to let these calculations work with my data.
6. Chart Value
Below is how the various slices fit together to give the gauge chart view.
How to Create the Small Multiple Matrix
Below are the calculations for the small multiple.
- Column Divider: (Index ()-1) % (Round (Sqrt (Size ())))
- Row Divider: Int ((Index ()-1)/ (Round (Sqrt (Size ()))))
These calculations tell tableau how to split the views into equal parts. As explained by Tableau Zen master Chris Love, the INDEX function counts the rows and gives ” a zero-based position of our dimensions, then we take modulus with respect the square-root of the number of dimensions”. You can read further about these calculations here.
Steps for Creating the Small Multiple Gauge Chart
- First, I created a donut chart using the Number of Records measure.
- Next, I dragged the Food and Slice onto the Detail shelf of Marks. The drop-down menu on Market has been set to Pie chart.
- On the same shelf, I put the Chart Value measure on the angle and set the aggregation to AVG.
- Then, I placed the Food on the Detail shelf for the circle chart and placed both the Portion size (cals) and the Food fields on the Label card.
- I then placed the color field on the Color shelf and edited the color legend to be red, grey and white for the for colored, grey and hidden sections. The white is used to hide the lower half of the circle so that we only have a semi-circle view.
- I placed the Category field on the Filter shelf and selected one of the options.
- Next, I placed the column divider and row divider on the column and row shelves respectively and set them to compute using the Food dimension, by clicking the drop-down arrow on these respective fields
- In the last step, I then removed the Headers from the chart and formatted the borders, by removing the row grid lines
All in, this chart took me about 45 minutes to complete. The resulting view looked like this:
You can download a copy of this dashboard in the link here. Now, it’s your turn readers. What do you think of this viz? Worth it? Insightful? Cool? Can you think of use cases where this would be helpful or useful? Would you ever use this or not and why?