<< Back

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:

1 numbers

And what’s even nicer is that you can alter the measure’s default number format to automatically round a specific unit, like this:

2 round to k.png

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:

3 populations.png

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:

4 populations k.png

And if we round to the millions, we get this:

5 populations m.png

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:

6 pop rounded optimised

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:

7 boom

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"
STR(ROUND(AVG([Population Total]),0))

Gwilym Lockwood

London, UK

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

  1. 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)

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

    // Number (Dynamic)
    IIF(ISNULL(MIN([Number])),'',STR([Number Format (#)]) + [Number Format (Units)])

    1. 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
      //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)
      //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 ''

  2. 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)

    // Duration (Dynamic)
    CASE SIGN([Date Diff (Second)])
    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’,”)

Leave a Reply

Your email address will not be published. Required fields are marked *