Ads Top

Percentage Gauges in Tableau

For Makeover Monday, Week 2, Andy Kriebel used Highcharts to create a nice dashboard, which used a series of gauges (click on the link to see the interactive version):


In this case, I thought the gauge charts worked pretty well. Gauges are a default chart type in Highcharts, but Tableau doesn’t do gauges out of the box. So, opportunist that I am, I figured that I’d write a quick blog post on how to create charts like these in Tableau.

The Gauge
A few months ago, I wrote a blog on creating NPS gauge charts in Tableau. When I saw Andy’s visualization, it struck me that the Highcharts gauges were pretty similar to my NPS gauges. So, I figured I’d be able to pretty easily modify my NPS methodology to work for a percentage gauge. In case you haven’t read my post on NPS gauges, let’s start with observations about this chart.

First of all, it looks basically like a half of a donut chart (and donut charts in Tableau are essentially pie charts with a hole in the middle). The first slice of a pie chart in Tableau always starts at the 12:00 mark, but this chart would need to start at 9:00. The values of the chart will need to go from 0, starting at 9:00, to 100, ending at 3:00. This is the biggest challenge.

My approach creates a donut chart with 5 pre-defined slices, as shown below.



Slice 1 – Variable sized slice starting at 12:00 and ending between 12:00 and 3:00. This slice will be used to show the second part of the percentage (if the value is greater than 50). Otherwise, this slice will have 0 size, making it invisible.
Slice 2 – Variable sized slice starting and ending somewhere between 12:00 and 3:00, after slice 1. If the percentage is less than 100, then this slice will be visible and will appear in a light grey color.
Slice 3 – Hidden slice starting at 3:00 and ending at 9:00. This slice will always be the same color as the background, rendering it invisible, essentially making it a half-donut.
Slice 4 – Variable sized slice starting at 9:00 and ending between 9:00 and 12:00. This slice will be used to show the first part of the percentage (values between 0 and 50).
Slice 5 – Variable sized slice starting and ending somewhere between 9:00 and 12:00, after slice 4. If the percentage is less than 50, then this slice will be visible and will appear in a light grey color, like Slice 2.

The data set for this chart is exactly the same as the one I used for the NPS chart. It contains five records, one for each slice, with the following information about each:

Slice # – Numeric identifier of the slice (1, 2, etc.)
Slice – Name of the slice (Slice 1, Slice 2, etc.)
Details – Description of the slice and how it’s used.

Strictly speaking, we could get by with just Slice # in the data set, but I decided to add a bit more information to make it less confusing to someone using the template.

There are no measures in the data set. We’ll create the measure within Tableau, based on the percentage. Note: I’m assuming that you will have a percentage which can be fed to the visualization. For demonstration purposes, I’ll just create a parameter allowing you to specify the percentage.

The Calculations
Calculating the pie chart size measure is a matter of some relatively simple math. To simplify my formulas, I created five calculated measures, one for each slice. Here are the formulas for each.

Value_Slice_1:  IF [Percentage]>.5 THEN [Percentage]-.5 ELSE 0 END
Value_Slice_2:  IF [Percentage]>.5 THEN 1-[Percentage] ELSE .5 END
Value_Slice_3:  1
Value_Slice_4:  IF [Percentage]<.5 THEN [Percentage] ELSE .5 END
Value_Slice_5:  IF [Percentage]<.5 THEN .5-[Value_Slice_4] ELSE 0 END

Note: “Percentage” is the name of the parameter noted earlier.

Then, I combine these values into a single measure, which I call Chart Value:

CASE [Slice #]
WHEN 1 THEN [Value_Slice_1]
WHEN 2 THEN [Value_Slice_2]
WHEN 3 THEN [Value_Slice_3]
WHEN 4 THEN [Value_Slice_4]
WHEN 5 THEN [Value_Slice_5]
END

Finally, I create a calculated field called Color:

IF [Slice #]=3 then "Hidden"
ELSEIF [Slice #]=5 OR [Slice #]=2 THEN "Grey"
ELSE "Colored"
END

Next, based on these calculations, I create a very simple pie chart, as shown below:

  
I then change to a dual-axis chart, with the second axis showing a manually sized white circle.


Finally, I drag Percentage to the label card to add the text.


From here, you can add your sheet to a dashboard. In order prevent people from interacting with the individual slices of the pie, I’d recommend floating a blank over top of it.

Here’s the final result:



Should You?...
Before I wrap this post, I think it's really important to address the question of whether or not you should use this type of chart. And, as always, the answer is "it depends." But, in most cases, you probably shouldn't. Gauges can be good when they are used to display a standalone KPI, as they are easy to understand. But they are pretty bad in situations where you're comparing multiple values. Our eyes simply aren't good at distinguishing the lengths of arcs, whereas are eyes are fantastic at distinguishing the lengths of 2 dimensional lines (i.e. bars). So, in these cases, you will definitely be better with a bullet chart or some other more linear visual. Plus, these are way easier to create in Tableau. For more on this topic, I'd recommend the following blogs:

Alternatives to NPS Gauges - My exploration of alternatives to gauges, including bullet charts.

Accurate vs. Emotional Comparisons - Zen Master, Steve Wexler, discusses the use of bars, pies, and other chart types and when to use them.

All this being said, I'd simply caution you if you're thinking about using gauges. I share this technique not because it's the way you should do it, but because there could be a time where it makes sense, for whatever reason. But you should avoid using a chart like this simply because it looks cool. While visual appeal is often important, you can still create visualizations with both visual appeal and strong analytical precision at the same time.

Required Files
If you have a need to create a gauge like this, then feel free to download my examples from Tableau Public. You can also find the Excel template here.

Ken Flerlage, January 8, 2018

9 comments:

  1. Hi,
    How do you get the Slice # parameter?
    Do you create a table with slice # in the db? and how do I connect this table with my measures source/??

    ReplyDelete
    Replies
    1. That's a dimension in the data file. You can find the sample Excel sheet here: http://www.kenflerlage.com/2017/07/creating-nps-gauges-in-tableau.html

      Delete
    2. great solution but i'm struggling to figure out how to integrate my data source that has actual percentages that I want to use with the slices template you've demonstrated. I looked at your sample excel sheet and it was just the slices data. While the parameter approach allows me to input and number, how would I join/link the slices data to my own percentage data that I want to display?

      Delete
  2. Exactly what I needed for a project I'm working on. Fantastic tutorial!

    ReplyDelete
  3. Great tutorial ... Is it possible to replace percentage with a calculated field?

    ReplyDelete
    Replies
    1. Yes, should be able to do that pretty easily. I just used a parameter for demonstration purposes.

      Delete
  4. I tried but this is returning an error in the calculation "Chart Value", error of comparison with aggregation

    ReplyDelete
    Replies
    1. Here are some tips on how to deal with that error: https://kb.tableau.com/articles/issue/error-cannot-mix-aggregate-and-non-aggregate-arguments-with-this-function-when-creating-a-calculated-field

      Also, I'm happy to help you with it if you can send me some details via email.

      Delete

Powered by Blogger.