• matthewritchie2

Gauge Charts without Data Densification

Tableau is an amazing tool that has created and fostered a community of data users passionate about data visualization. As a testament to that passion, we spend our time learning the ins and outs of data tools we use, figuring out what is possible, pushing its boundaries, and understanding why a tool is designed the way it is.

Not long after picking up Tableau, you begin to learn what visualization best practices are. Some best practices are chiefly important, like not overloading the user senses with an amalgamation of clashing colors and myriad of shapes / icons; others, like chart choice, are slightly peripheral and may be OK to break given the right circumstances.

One project I was working on recently had such a circumstance come up. Users wanted a % achievement KPI across 5 groups to be shown as a gauge chart. A gauge chart is essentially the top half of a donut chart, and a donut chart is a pie chart with a hole in the center (ad-lib food joke). Normally, best practices tell us to the use something like a bar chart; a visualization that makes it easy to compare across groups based on the relative height of the bars. Although a bar chart is a better choice from a best practice standpoint, there are a few reasons why I rationalized that a gauge chart could be ok:

  1. The space within the gauge leaves plenty of room for a BIG KPI number

  2. There was less focus on comparing across the groups

  3. The groups were still sorted in descending order so relative ranking was still possible

  4. Users found it more appealing / exciting to look at in this context.

Once I rationalized the decision to put a gauge, there was one hurdle left. At the time (April 16th 2020), there was no documented way of building out a gauge chart without densifying data. There was, however, a picture which I came across on the Flerlage twin’s blog post for Gauge Charts that made me think it should be possible to recreate without data densification.

The only hurdle is getting the ordering of the slices correct without the use of dimensions. Accomplishing this proved to be more simple than I had anticipated; all it took was understanding how Tableau would display the measures we add to our viz. Below are step-by-step instructions on how to create this chart; you can also download a packaged tableau workbook that has the final product and all calculations required at the bottom of the page.

How to:

Note: this can work for any numerical measure as long as you can express it as a part to whole relationship.

Creating the Framework for a Donut Chart:

  1. Add any dimensions desired to the columns and rows shelf

  2. Create a Dummy calculated field with the formula as MIN(0) and add it to the columns shelf

  3. Fun tip: You can also double click into the columns shelf and type in MIN(0) without adding a dummy calculation to the fields in your data pane.

  4. Duplicate the calculation (hold ctrl and click and drag the pill next to the original) so that it is on the columns shelf twice and make it into a dual-axis chart.

  5. It may not be required but I still synchronize my dual axis always out of habit

  6. Hide the headers and edit the line formatting so that we don’t have any rogue lines

  7. Change the marks type for the left MIN(0) to be a pie chart and the right MIN(0) to be a circle.

  8. Change the color of the circle to match the background of your dashboard and adjust the size.

When you finished, your chart should look similar to this:

Creating the Donut:

  1. Make your calculated field which is a percentage (maximum of 100%) that you want to show.

  2. If you name it "Donut Percentage" you can copy and paste the calculated fields I have written below.

  3. Make 5 additional calculated fields; 4 of these (a - d) will use your percentage from step 1

  4. First Quadrant Full:

  5. IF [Donut Percentage] < .5 then [Donut Percentage] ELSEIF ISNULL([Donut Percentage]) Then 0 ELSE .5 END

  6. First Quadrant Empty:

  7. IF [Donut Percentage] < .5 then .5 - [Donut Percentage] ELSEIF ISNULL([Donut Percentage]) then .5 END

  8. Second Quadrant Full:

  9. IF [Donut Percentage] > .5 then IF [Donut Percentage] < 1 then [Donut Percentage] - .5 ELSE .5 END END

  10. Second Quadrant Empty

  11. IF [Donut Percentage] > .5 then IF [Donut Percentage] < 1 then1 - [Donut Percentage] ELSE 0 END else .5 END

  12. Donut Bottom:

  13. MIN(0)

  14. Next is the trick to make it display appropriately with only using the measure values pill; Add all the calculated fields you created and re-order them so they pills are sorted like so:

  15. Second Quadrant Full

  16. Second Quadrant Empty:

  17. Donut Bottom

  18. First Quadrant Full

  19. First Quadrant Empty

  20. This works because tableau starts displaying the marks in the First Quadrant and works clockwise until all pills are displayed; by making the bottom equal to the sum of the top, we ensure that the Donut Bottom calc will always take up the bottom half of the donut.

  21. Lastly, we need to change the color of the measure names as follows:

  22. First Quadrant Full and Second Quadrant Full: Whatever color you want to represent the full portion

  23. First Quadrant Empty and Second Quadrant Empty: A lighter color to represent the empty portion. I like light grey for this

  24. Donut Bottom: Match the background color for your dashboard / viz.

Your viz should look similar to this when you are finished:

Now that we have our gauge, our goal is technically accomplished. However, as I alluded to in the intro, we can utilize the white-space in the center to offset the choice of the viz by adding more information. One simple way to do this is by:

Adding Some Polish:

  1. Drag the [Donut Percentage] Calculated field onto the marks card for the secondary axis as a label.

  2. Format as a percentage if needed, and edit the size and weight of the font so it stands out without extending beyond the inner circle.

  3. Add new lines in the label text field after the percentage to move the label higher so that it sits in the top half of the gauge

Additional Options for Improvement:

To make a true gauge, we can add a pointer to show where the endpoint is.

If desired, we can take one of the short comings of these charts and use it to our advantage. Since it is harder for humans to discern differences between angles, we can hard code a small number that will be used to display the pointer without visibly distorting the view.

How to add a pointer:

  1. Create two calculated fields:

  2. First Quadrant Pointer

  3. IF [Donut Percentage] < .5 then .0075 else IF ISNULL([Donut Percentage]) Then 0 ELSE 0 END

  4. Second Quadrant Pointer

  5. IF [Donut Percentage] >= .5 then .0075 else 0 END

  6. c. Note: I chose .0075 (.75%) as the hardcoded number but you can choose anything. Just make sure it isn’t too big that it doesn’t distort the size and spacing of the viz too much.

  7. Place each pointer calculation between the full and empty portion of each respective quadrant

  8. Change the color of the Pointer marks to be something darker and contrast to the color used to fill the gauge.

Here is the finished product:

And that concludes the lesson. Thank you for reading – I hope you found it helpful. Feel free to comment below and/or share this post if you found it helpful.

Gauge Without Data Densification Sample
• 1.15MB