Strings and roundabouts: how to label your numbers in Tableau effectively
Lots of Tableau dashboards feature big summary numbers. They’re pretty nice, and they look like this:
And what’s even nicer is that you can alter the measure’s default number format to automatically round a specific unit, like this:
But sometimes, the range of numbers is a lot wider than sales per state in Superstore. Let’s have a look at population per country in the World Indicators dataset:
Those are some long numbers, so let’s round them to the nearest unit again. But which one? If we round to the thousands, we get this:
And if we round to the millions, we get this:
It’d be great if we could get Tableau to figure out what the nearest sensible unit is. That functionality doesn’t exist yet (as far as I know!), but we can write a specific optimised rounding calculation for labelling purposes. It’s a bit of a long one:
This calculation returns the number you want as a string. It does this by:
- Aggregating the number you’re actually working with already and finding out whether it’s above a billion (in which case you’d want to summarise to whatever number of billions it is), or above a million (in which case you’d want to summarise to whatever number of millions it is), and so on.
- Converting it to an absolute number so that it works for negative numbers too.
- Taking that aggregated number and dividing by the sensible unit. For example, if your number is 34000000, you’d want to express it as 34 million, so we’re dividing it by a million to return 34.
- Rounding that divided figure to one decimal place. This is just my preference, you can do what you like! Set the number to 0 for no decimal places, or 2 for two decimal places, etc.
- Convert that number to a string.
- Add a text unit abbreviation to the end of it.
Of course, you can also add trillions, quadrillions, and so on, if that’s what your data requires.
This sorts us out nicely:
Now, I’ve deliberately aggregated everything within the calculation, and I’m only using it for labelling purposes. I categorically do not recommend aggregating outside this calculation or using this calculation for calculating anything else. This will result in a mess of rounding errors which can seriously damage your data. But as a final step once you’ve sorted everything out, I find that this is really nice for presenting data.
Here’s the calculation in text so you can copy and paste it into your workbooks:
IF ABS(AVG([Population Total])) >= 1000000000 THEN
//round for billions
STR(ROUND(AVG([Population Total] / 1000000000), 1)) + "b"
ELSEIF ABS(AVG([Population Total])) >= 1000000 THEN
//round for millions
STR(ROUND(AVG([Population Total] / 1000000), 1)) + "m"
ELSEIF ABS(AVG([Population Total])) >= 1000 THEN
//round for thousands
STR(ROUND(AVG([Population Total] / 1000), 1)) + "k"
ELSE
STR(ROUND(AVG([Population Total]),0))
END
I do something very similar within my workbooks. Although, I create 3 calculations, one for the formatted number (as a Number), one for the unit (K/M/B/T) (as a String), and one that combines the two (as a String). I typically do it this way because I prefer reducing the font size of the unit separately in tooltips, but have the combination string for labeling.
// Number Format (#)
IF ISNULL(MIN([Number])) THEN 0 // Zero out Nulls (ZN)
ELSEIF ABS(MIN([Number])) < 10^03 THEN MIN([Number]) // Hundreds
ELSEIF ABS(MIN([Number])) < 10^06 THEN ROUND(MIN([Number]) / 10^03, 2) // Thousands (K)
ELSEIF ABS(MIN([Number])) < 10^09 THEN ROUND(MIN([Number]) / 10^06, 2) // Millions (M)
ELSEIF ABS(MIN([Number])) < 10^12 THEN ROUND(MIN([Number]) / 10^09, 2) // Billions (B)
ELSE ROUND(MIN([Number]) / 10^12, 2) // Trillions (T)
END
// Number Format (Units)
IF ISNULL(MIN([Number])) THEN '' // Null
ELSEIF ABS(MIN([Number])) < 10^03 THEN '' // Hundreds
ELSEIF ABS(MIN([Number])) < 10^06 THEN 'K' // Thousands (K)
ELSEIF ABS(MIN([Number])) < 10^09 THEN 'M' // Millions (M)
ELSEIF ABS(MIN([Number])) < 10^12 THEN 'B' // Billions (B)
ELSE 'T' // Trillions (T)
END
// Number (Dynamic)
IIF(ISNULL(MIN([Number])),'',STR([Number Format (#)]) + [Number Format (Units)])
Awesome Seth and Gwilym! I use a similar technique to get Custom Number formats with 1 decimal place.
//Determine if the value is negative
IF SUM([Value]) < 0 THEN '-' ELSE '' END
+
'$' //Dolar Symbol
+
//Get Int Amount by Dividing the Value by its' Base 1000 Value
STR(INT(SUM([Value])/POWER(1000,INT(LOG(SUM([Value]),1000)))))
+
//Determine if you need to show a decimal
//If the Modulo is zero, we just want to show blank (Ex 159.0K = 159K)
//Else we want to show the decimal value (Ex 159.4K)
IF (ROUND(SUM([Value])/POWER(1000,INT(LOG(SUM([Value]),1000))),1)*10)%10 = 0 THEN ''
ELSE '.' + STR((ROUND(SUM([Value])/POWER(1000,INT(LOG(SUM([Value]),1000))),1)*10)%10)
END
+
//Determine the Unit to display based on the Log Base 1000
CASE INT(LOG(SUM([Value]),1000)) //Unit Symbol
WHEN 1 THEN 'K'
WHEN 2 THEN 'M'
WHEN 3 THEN 'B'
ELSE ''
END
Ah, nice! I like the way you’re removing the “.0” and just showing the whole number.
You can also do something similar with date durations with a few calculated fields.
The Duration (Dynamic) creates a time duration in whole unit values.
For Example:
The difference between Now (09/28/17 10:13:46 AM) and a past date (05/08/17 01:10:29 PM) is 12344597 seconds or rounded up to the nearest day of 143 days.
However, with the Duration (Dynamic) calculation, you can see exact values, which I think gives you a better understanding than using parts of days, weeks, etc.
So the exact value in this case would be 142d 21h 3m 17s.
If a date exists in the future, this calculation will prepend a +.
// Date Diff (Second)
DATEDIFF(‘second’,[Date],NOW())
// Duration (Dynamic)
CASE SIGN([Date Diff (Second)])
WHEN NULL THEN ”
WHEN 1 THEN
IIF(FLOOR([Date Diff (Second)]/86400)>0,STR(FLOOR([Date Diff (Second)]/86400)) + ‘d ‘,”) +
IIF(FLOOR([Date Diff (Second)]%86400/3600)>0,STR(FLOOR([Date Diff (Second)]%86400/3600)) + ‘h ‘,”) +
IIF(FLOOR([Date Diff (Second)]%86400%3600/60)>0,STR(FLOOR([Date Diff (Second)]%86400%3600/60)) + ‘m ‘,”) +
IIF([Date Diff (Second)]%86400%3600%60>0,STR([Date Diff (Second)]%86400%3600%60) + ‘s’,”)
WHEN -1 THEN
‘+ ‘ +
IIF(FLOOR(ABS([Date Diff (Second)])/86400)>0,STR(FLOOR(ABS([Date Diff (Second)])/86400)) + ‘d ‘,”) +
IIF(FLOOR(ABS([Date Diff (Second)])%86400/3600)>0,STR(FLOOR(ABS([Date Diff (Second)])%86400/3600)) + ‘h ‘,”) +
IIF(FLOOR(ABS([Date Diff (Second)])%86400%3600/60)>0,STR(FLOOR(ABS([Date Diff (Second)])%86400%3600/60)) + ‘m ‘,”) +
IIF(ABS([Date Diff (Second)])%86400%3600%60>0,STR(ABS([Date Diff (Second)])%86400%3600%60) + ‘s’,”)
END
+1 for the calc suggestions, +1 for the Poorly Drawn Lines avatar.