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.

Update March 15, 2019: A common problem with this blog has been an inability to make it work with your own data instead of the parameter. I apologize for that as I failed to properly explain how to do it. If you are struggling with this, I'd refer you to the following post on the Tableau Community Forum in which I show exactly how to do this: Percentage Gauges with Your Own Data


Ken Flerlage, January 8, 2018

49 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
    3. I'm having the same issue as Elizabeth.

      Delete
    4. You'd have to join in your own data set. If you could send an email to flerlagekr@gmail.com with some sample data, I could show you how to do it.

      Delete
    5. Hi Ken,
      I have issue about "Slice #" and I sent a mail for you!
      Thanks so much!

      Delete
    6. Hi as data is a confidential, can you provide an example of how to join the sheet of Slices # into the DB... how do you define the relationship to join the DB?

      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
  14. Hi Ken,

    For some reason, color and slice fields are in measures in my dataset and i am unable to convert to dimension and it ends up getting aggregated. Please advise.

    ReplyDelete
    Replies
    1. Right-click on the fields (on the left hand side) and choose the "Convert to Dimension" menu. That should change them to dimensions (and will make them discrete), which should prevent the automated aggregation.

      Delete
  15. Hey Ken,

    I have it working and have a MS SQL data source that I need to use to get the % to feed into the percentage measure, but when I try to refer to it it wants me to build a relationship,-

    First just tried referencing the variable directly (Tried window_sum([otherdataset].[PercentageCalculated]) for example) and it wants me to create a relationship which I don't know that I can.

    When I try window_sum

    the problem I'm having is that the MSSQL data set is huge and I'm not sure how I would be able to link them together. (Tried window_sum([otherdataset].[PercentageCalculated]) for example but tried other variations). Pretty new to Tableau so hopefully this isn't too obvious of an issue :-)

    ReplyDelete
    Replies
    1. Any chance you could send me an email offline? flerlagekr@gmail.com

      The size of the data could be a problem, but we may be able to find a solution.

      Delete
  16. Hi Ken,

    Sent you an email with some sample data and the question of the hour. Trying to get this in sync with our data vs the data template you provided was an exercise in frustration as we are probably missing something simple. Any assistance would be greatly appreciated.

    Hector

    ReplyDelete
  17. Hi Ken I want to show 10 to 20 range values instead of 0 to 100,how can this achieve

    ReplyDelete
    Replies
    1. Well, the simplest way might be to subtract 10 from your value then multiply by 10. That will change 10 to 0, 15 to 50, and 20 to 100, allowing you to trick it into thinking it's a range of 0 to 100. Then you'd just display the actual value instead of the "fake" value.

      Delete
  18. Hi Ken, thanks for the example. But I find it hard to follow your instructions as my result is not the same as you. How can I use hidden color btw??
    and when I use 'Dual Axis', it's still pie chart (doesn't have a hole in its center)

    ReplyDelete
  19. Hi Ken!
    Thanks for the explanation, but somehow in my case the color is not hidden (still have color in slice #3). How can I use hidden color?
    And after I check 'dual axis', it's still pie (doesn't have a hole in its center)

    ReplyDelete
    Replies
    1. Might be best to deal with this offline. Can you send me an email at flerlagekr@gmail.com?

      Delete
  20. Hey Ken,

    Thank you for your many posts and examples you have shown. I have read a number of them as I am a relatively new user of Tableau. With that though, I have followed all of your examples on this but I'm trying to apply this to my data that I want to use and I would like to use a Calculated Field instead of a parameter as you did. I have all of that done but my calculated field is using a sum which causes me an error on the "Chart Value" equation because of the aggregate and non-aggregate error. Any thoughts? I appreciate the insight.

    Thanks.

    ReplyDelete
    Replies
    1. That's a tricky little problem and is difficult to troubleshoot without seeing the workbook. Any way you could send me an email at flerlagekr@gmail.com?

      Delete
  21. i think a video from start to finish would help a lot if you're trying to make this work with your own data set. i just have a decimal for the monthly figure as my result and the 5 slices seem more involved than needs to be. really cool graph if i could make it work.

    ReplyDelete
    Replies
    1. Feel free to email me at flerlagekr@gmail.com if you need some assistance.

      Delete
  22. I have had to create an aggregate percentage to capture the % of a condition to a whole. Struggling to get that to be representative of the condition on an individual gauge The percentage does not retain the overall percent to whole. Any help would be welcome

    ReplyDelete
    Replies
    1. Difficult to troubleshoot here, but feel free to send me an email at flerlagekr@gmail.com.

      Delete

Powered by Blogger.