28 September 2017
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:
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:
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.
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 billionsSTR(ROUND(AVG([Population Total] / 1000000000), 1)) + 'b'ELSEIF ABS(AVG([Population Total])) >= 1000000 THEN//round for millionsSTR(ROUND(AVG([Population Total] / 1000000), 1)) + 'm'ELSEIF ABS(AVG([Population Total])) >= 1000 THEN//round for thousandsSTR(ROUND(AVG([Population Total] / 1000), 1)) + 'k'ELSESTR(ROUND(AVG([Population Total]),0))END