Mastering Gauge Charts in Tableau
Step by Step Tutorial
You know what clients love? Pie charts. You know what else they love? Speedometers and gauges. Should they love them? Probably not, but it’s not our place to argue. Problem is, Tableau doesn’t offer gauge charts out of the box. But that’s the thing about Tableau, if you can think outside the box, they make it possible to create almost anything.
Case and Point:
So how do we create a gauge chart in Tableau? We simply manipulate the pie chart. We will need fields that represent actual values and of course, the target we are looking to achieve. It is also important that these values are calculated as aggregate percentages out of total, rounded to zero decimal places. So you’re looking to represent the figures as (for example): 20% achieved out of a possible 100% of total, with an acceptable target of 80%. The gauge itself will represent 100% starting from the left (0%) and moving towards the right.
Not a fan of reading? Watch our video instead: Youtube
For this demo, I’ve based these two values on parameters and referenced those parameters in relevant calculations.
Firstly, create two axes using SUM(0) and convert them to a dual axis. Select the first axis and place Measure Names onto ‘Colour’ and Measure Values onto ‘Angle’. If you’d like some visual guidance in understanding the ‘why’ behind the logic involved, temporarily add Measure Values to ‘Label’ as well.
You will need to include 13 very specific calculations in the Measure Values and order them according to this list below:
You can obviously name these fields whatever you want, but I suggest you retain a type of numbering system just to make things easier.
If you have the kind of mind that needs to understand the “why’s” behind both the order and logic used, you can jump to the end of this post to read the full explanation. I thought I’d make that part optional, as I know there are those out there who just want the recipe, not a lesson in cooking.
Remember that I’m basing my ‘Achieved’ and ‘Target’ figures on percentage-based parameters, but you’ll likely have your own aggregate calculations / fields to represent these values.
Now, decide on what three colour you will used to represent ‘achieved’ values, the ‘target’ line and any remaining space, or what was ‘unachieved’.
You can use this legend as a guide to which of field ‘number’ your 3 colour choices should be used with. Note that the 7th field has a completely different colour from the rest, and this is because it will represent the bottom half of the pie chart that is meant to appear as invisible; so this colour should be based on whatever the background colour of your view or dashboard will be.
The logic for the 13 calculations used as Measure Values is as follows (full disclaimer: these can probably be neatened somewhat, but I’ve found through painful trial and error that messing with the order of the conditions in these if statements has broken the functionality of the graph many times, so personally I’d rather learn to live with anything that might seem unnecessarily out of order than to risk it breaking the view):
IF [Achieved] = 0.5 AND [Target] = 1 THEN 0
ELSEIF [Achieved] > 0.5 AND [Target] = 0.5 THEN 0
ELSEIF [Achieved] = 1 AND [Target] = 1 THEN 0.5 - 0.02 //0.48
ELSEIF [Achieved] > 0.5 AND [Target] > 0.5 AND [Target] < [Achieved] THEN ([Target] - 0.5) - 0.01
ELSEIF [Achieved] > 0.5 AND [Target] >= [Achieved] THEN [Achieved] - 0.5 - 0.01 END
IF [Achieved] = 1 AND [Target] = 1 THEN 0
ELSEIF [Achieved] > 0.5 AND [Target] = [Achieved] THEN 0
ELSEIF [Achieved] <= 0.5 AND [Target] = 1 THEN [Target] — 0.5–0.02
ELSEIF [Achieved] <= 0.5 AND [Target] > 0.5 THEN [Target] — 0.5–0.01
ELSEIF [Achieved] > 0.5 AND [Target] > [Achieved] THEN [Target] — [Achieved] — 0.01
END
IF [Target] = 0.5 THEN 0.01
END
IF [Target] > 0.5 AND [Target] <= 1 THEN .02 END
IF [Achieved] = 0.5 AND [Target] = 0.5 THEN 0
ELSEIF [Achieved] > 0.5 AND [Target] = [Achieved] THEN 0
ELSEIF [Achieved] > 0.5 AND [Target] < 0.5 THEN [Achieved] — 0.5
ELSEIF [Achieved] > 0.5 AND ([Target] > 0.5 AND [Target] < [Achieved]) THEN [Achieved] — [Target] — 0.01
ELSEIF [Achieved] > 0.5 AND [Target] = 0.5 THEN [Achieved] — [Target] — 0.01
END
IF [Achieved] <= .5 AND [Target] = 1 THEN 0
ELSEIF [Achieved] > .99 THEN 0
ELSEIF ([Achieved] < 0.5 AND [Target] = [Achieved]) OR ([Achieved] <= 0.5 AND [Target] < 0.5) THEN 0.5
ELSEIF [Achieved] < 0.5 AND [Target] > 0.5 THEN 0.5 — ([Target] — 0.5) — 0.01
ELSEIF [Achieved] < 0.5 AND [Target] = 0.5 THEN 0.5–0.01
ELSEIF [Achieved] = 0.5 AND [Target] = 0.5 THEN 0.5–0.01
ELSEIF [Achieved] = 0.5 AND [Target] > 0.5 THEN 1 — [Target] — 0.01
ELSEIF [Achieved] > 0.5 AND [Target] <= 0.5 THEN .5 — ([Achieved] — 0.5)
ELSEIF [Achieved] > 0.5 AND [Target] > 0.5 AND [Target] < [Achieved]
THEN 0.5 — [5. Surplus >= 50%] — [1. Achieved >= 50%] — 0.02
ELSEIF [Achieved] > 0.5 AND [Target] < [Achieved] THEN ZN([5. Surplus >= 50%])
ELSEIF [Achieved] > 0.5 AND [Target] = [Achieved] THEN 0.5 — [1. Achieved >= 50%] — .02
ELSEIF [Achieved] > 0.5 AND [Target] > [Achieved] THEN 0.5 — [1. Achieved >= 50%] — [2. Shortfall > 50%] — 0.02
ELSE 0
END
Surely I don’t need to paste the text for this, right? Remember that this field represents the invisible ‘bottom half’ of the pie chart, and because it is supposed to represent a value of 1 here, make sure to use the MIN() aggregation when placing it in Measure Values.
IF [Achieved] = 1 AND [Target] = 1
THEN 0.5
ELSEIF [Achieved] < 0.5 AND [Target] < [Achieved]
THEN [Target] — 0.01
ELSEIF [Achieved] < 0.5 AND [Target] < 0.5 AND [Target] > [Achieved]
THEN [Achieved] — 0.01
ELSEIF [Achieved] < 0.5 AND [Target] = [Achieved]
THEN [Achieved] — 0.01
ELSEIF [Achieved] < 0.5 AND [Target] > 0.5
THEN [Achieved]
ELSEIF [Achieved] < 0.5 AND [Target] = 0.5
THEN [Achieved]
ELSEIF [Achieved] = 0.5 AND [Target] < 0.5
THEN [Target] — 0.01
ELSEIF [Achieved] = 0.5 AND [Target] = 0.5
THEN 0.5–0.01
ELSEIF [Achieved] = 0.5 AND [Target] > 0.5
THEN 0.5
ELSEIF [Achieved] > 0.5 AND [Target] < 0.5
THEN [Target] — 0.01
ELSEIF [Achieved] > 0.5 AND [Target] = 0.5
THEN 0.5–0.01
ELSEIF [Achieved] > 0.5 AND [Target] > 0.5
THEN 0.5
END
IF [Achieved] < 0.5 AND [Target] < 0.5 THEN
IF [Target] >= [Achieved] THEN [Target] — [Achieved] END
ELSEIF [Achieved] < 0.5 AND [Target] = 0.5 THEN 0.5 — [Achieved] — 0.01
END
IF [Target] < 0.5 THEN .02 END
IF [Achieved] < 0.5 AND [Target] = [Achieved] THEN 0
ELSEIF [Achieved] < 0.5 AND [Target] < [Achieved] THEN ([Achieved] — [Target]) — 0.01
ELSEIF [Achieved] = 0.5 AND [Target] < 0.5 THEN 0.5 — [Target] — 0.01
ELSEIF [Achieved] > 0.5 AND [Target] < 0.5 THEN 0.5 — [8. Achieved < 50%] — 0.02
ELSE 0
END
IF [Achieved] < 0.5 AND [Target] = [Achieved] THEN 0.5 — [Achieved] — 0.01
ELSEIF [Achieved] < 0.5 AND [Target] < [Achieved] THEN 0.5 — [Achieved]
ELSEIF [Achieved] < 0.5 AND [Target] < 0.5 THEN 0.5 — [8. Achieved < 50%] — 0.02
ELSEIF [Achieved] < 0.5 AND [Target] = 0.5 THEN 0.5 — [Achieved] — 0.01
ELSEIF [Achieved] < 0.5 AND [Target] > 0.5 THEN 0.5 — [Achieved]
END
- ZN([9. Shortfall < 50%])
IF [Target] = 0.5 THEN -0.01 END
For the second axis, we want this to represent the hollow inside and you should set the type of graph to ‘circle’ and increase the size to your liking. You should also set the colour to match the colour used for the 7th field in the 1st axis; the one that represents the ‘bottom invisible half’.
Finally, to tidy things up, you can hide both axes and format the graph to hide all lines that represent grid lines, reference lines, etc.
Lastly, you can add your ‘actual’ and ‘target’ values to the label on the 2nd axis. Be sure to hit ‘enter’ a few times and then ‘spacebar’ once, to add some dummy space below the text, so that it appears more centered with the top-half of the pie chart, rather than its center.
If you view my workbook to Tableau Public Here, you can play around with the ‘actual’ and ‘target’ value parameters to see live changes in the gauge chart, giving you a better sense of how it works.
A word of caution: I’ve noticed that sometimes, when both my parameters happen to share the same value, Tableau does not recognize this condition as TRUE and therefore any calculations relying on this logic stop working. I reported the bug to Tableau a while ago, who claimed to have fixed the issue in future version releases. However, I’ve still run into the issue from time to time. So if that happens to you out of the blue, refresh the view or close and reopen my workbook to continue testing and it should continue to work.
So for those of you who want to understand the how’s and why’s, let’s get into it:
Tableau pie charts typically begin and end like a clock, from 12 to 12. However, a gauge chart works from left to right, or speaking in terms of a clock-face, from 9 to 3.
This means that in order to imitate a gauge chart using a pie chart as a base, we have to split our pie chart into three main sections. The first is between 12–3, which will represent the gauge values between 50–100%; the second is the entire bottom half, or 3–9 and the third and final section is between 9–12, which will represent the gauge values between 0–50%. The pie chart’s starting point beginning at 12 is the main reason why the order of Measure Value fields is so important.
Basically, any part of the gauge chart that falls between its 50–100% range should come first in the order of fields, so that it appears between 12 and 3 in the pie chart’s order. It will then be followed by the ‘invisible’ bottom half, and lastly, any part of the gauge chart that falls between its 0–50% range will come last in the order of fields, so that it appears between 9–12 on the pie chart.
The series of ‘if’ statements used first determine whether the ‘actual’ values used fall within the 0–50% range or the 50–100% range, and similarly for the ‘target’ values.
They then look to determine whether the ‘actual’ value is less than, equal to or greater than the ‘target’ value, because this will determine whether any deficit or ‘shortfall’ space needs to be created between the ‘actual’ and ‘target’ values, or whether any surplus or ‘excess’ space needs to be created after the target value. Then it needs to consider whether these deficits and surpluses are to fall in the 0–50% or 50–100% range, or even across both.
Then of course, it needs to do the same for any ‘remaining’ or neutral space that makes up the 100% range when all the rest is already calculated and placed. Yeah… it’s quite tricky.
Image 1: ‘Achieved’ is less than ‘Target’, and both are below 50%, so both the space representing the ‘deficit’ or ‘shortfall’ as well as the blank space that comes after the target line and falling below 50% need to be calculated in the fields that come last in the Measure Values list. The remaining 50% to be used between the 50–100% range will be populated inside a different calculation, because it will be one of the first fields in the order of Measure Values.
Image 2: ‘Achieved’ is greater than ‘Target’, and both are below 50%, so this time the ‘surplus’ or ‘excess’ needs to be calculated in addition to the blank space that comes before the 50% mark.
Image 3 shows the ‘Target’ below the 50% mark but that ‘Achieved’ falls within the 50–100% range, so the ‘surplus’ needs to be calculated before the target line and after the target line within the 0–50% range as, and then separately again for where it falls in the 50–100% range.
… and so on and so forth, for the remaining images.
So you can see how all the calculations need to talk to each other and determine how much to populate and whether to populate at all based on what the other calculations are doing.
The target line itself is also a tricky one, because it technically has 4 different versions. These are based on whether it falls between the 0–50% range, the 50–100% range and whether it happens to be exactly 50%. If it’s exactly 50%, then we’ll need to split the target line into two separate versions — one for each of the two ranges. This will ensure the graph doesn’t move slightly off centre, which would happen if we kept the target line only on one side in the case of a perfect 50%.
So there you have it; that’s what it takes to produce a gauge chart using Tableau!
General disclaimers: The visual won't work / will break if 'achieved' is ever greater than 100%; so in cases like these, you'd need to create an if statement that caps 'achieved' at 100%. Also, when copy and pasting the formulas below into Tableau, it may not register certain punctuations correctly, so '-' may not be read as 'minus' as intended and you'll need to manually replace.
By Leanne Gladwin
