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

28 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
  5. Ken - Great article! How to align the tableau chart like high charts (as shown above) without floating?

    ReplyDelete
  6. You could tile it. Floating is not required.

    ReplyDelete
  7. Hello Ken
    This is a very insightful blog. I was wondering if it's possible to develop this technique using % and over run scenarios?
    Meaning if my % is between 0 and 90% the area become red ( not on target). If between 90% and 110% is green ( im on target) if above 110% i display red only the proportional data related to over run ( X - 110%). Thanks for your comments.

    ReplyDelete
    Replies
    1. Interesting idea. I'm sure you could do something like that, but I'm not sure how'd you'd visualize the overrun %. Would you extend the arc further, past horizontal?

      Delete
  8. Hello, I'd loved this idea but I'm struggling to implement it using my data. I mean, it can be implemented with the parameter but how about using a calculated percentage that is changed with different dashboard filters?

    ReplyDelete
    Replies
    1. You'd just need to switch out the parameter value with your measure. I'm happy to help if you'd like to reach out to me via email. flerlagekr@gmail.com

      Delete
  9. what about if i want to go over the goal

    ReplyDelete
    Replies
    1. Then you've hit a limitation of this type of chart and you should really consider a different chart type. Ryan Sleeper just wrote about this recently, so I'd suggest you take a look at his recent blog: https://www.ryansleeper.com/dashboard-gauge-1-how-to-make-bullet-graphs-in-tableau/

      Delete
  10. Hi Ken! Thank you for this! It's actually perfect for what I need. The problem I'm having though is in rotating the chart so that the hidden portion is at the bottom. I searched online and the only solution I've found is to sort on Color. But no matter how I fix the sorting, my Slice 3 never gets in the bottom position neatly, like yours. It's always at an angle. Any suggestions on how to fix this?

    ReplyDelete
    Replies
    1. I've seen this before and it does typically come down to sort orders and the order of pills on the marks card. I'd suggest taking a look at my original workbook and make sure the pills are placed on the marks card in the exact same order. Also check the sorting to make sure that's the same.

      If that doesn't help, then I'll probably need to see a screenshot or a workbook. We'd need to do that offline, so you can email me at flerlagekr@gmail.com. I'm happy to help so don't hesitate to reach out.

      Delete
    2. Thanks again, Ken! I fixed it by dragging Slice to color instead of the calculated field Color. Then I assigned White to slice 3, the color I want to slices 1 and 4, and gray to slices 2 and 5. I figured the sorting order we really want is on Slice asc anyway.

      Now it's perfect!

      Delete
  11. Hey Ken, love the viz. I'm trying to recreate this using my own data-- I've swapped your parameter "Percent" for my own percentage field but it seems I still need the Slice# field. Would I go about recreating that by using some percentage to radians calculation?

    ReplyDelete
    Replies
    1. You'd still need all of the same calculations as I included above. You'd just swap out the [Percentage] parameter for your actual data field in each of the Value_Slice calculated fields.

      Delete
  12. Thanks for this, Ken!
    But I'm struggling on the basic. The measure "Chart value" contains the following "Slice #".
    --> is this a calculated field or how I get this?

    ReplyDelete
    Replies
    1. Slice # comes from the Excel file.

      Delete
    2. Would you mind contacting me via email at flerlagekr@gmail.com and supplying a sample data set? I can then show you how to bring it all together.

      Delete
  13. Hi Ken,

    I am struggling with the same issue as Gian. I have all the calculations per above, I created my own percentage using my own data, but I can't quite figure out how to get the Slice # and Slice. Ideally it would be derived from my data.

    Any suggestions?

    Thanks,

    Mike

    ReplyDelete
    Replies
    1. Would you mind contacting me via email at flerlagekr@gmail.com and supplying a sample data set? I can then show you how to bring it all together.

      Delete

Powered by Blogger.