 ## 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:

1. 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.
2. Converting it to an absolute number so that it works for negative numbers too.
3. 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.
4. 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.
5. Convert that number to a string.
6. 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```

London, UK

### 5 thoughts on “Strings and roundabouts: how to label your numbers in Tableau effectively”

1. Seth Walters says:

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)])

1. Rody Zakovich says:

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

1. Gwilym Lockwood says:

Ah, nice! I like the way you’re removing the “.0” and just showing the whole number.

2. Seth Walters says:

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. Gwilym Lockwood says:

+1 for the calc suggestions, +1 for the Poorly Drawn Lines avatar.