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