In Tableau visualisation, some numbers are really big, reached to millions even billions. So users may expect the numbers to be displayed as 1M, 1B, etc.
One difficulty to achieve the functionality is that, in Tableau, we could not simply use “Number Format” like we usually do in Excel. (As shown below.)
[>=1000000] ##.0,,”M”;[>=1000]$##.0,”K”; $0
I investigated into the issue and found out two solutions. Each has its advantages and disadvantages.
###Solution 1: By Using Tableau Formatting Skilfully
This method is referenced from Tableau Support Communities.
Firstly, We can create a calculated field:
Then, apply calculated field into workbooks with the following formatting:
Overall it’s a clever method. However, it could only distinguish two situations: like “M” and “K” in the example above. (Please correct me in the comments if I am wrong!) So what if we not only want “M”, “K”, but also “B”, etc.? We may need to find another solution.
###Solution 2: By Converting Number into String
To achieve this, I wrote a calculated field. It’s a little bit complex, but well solved the situation.
The method is cool! However, converting the number into string also has side effects, because it actually changed the data type. When exporting data from Tableau into Excel, users could never see the origin numbers. It’s not a perfect solution, either.