December 31, 2015

Customising Tableau Number Formatting

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:

IF SUM([Sales]) >= 1000000 THEN SUM([Sales])/1000000
ELSE -SUM([Sales])/1000
END

Then, apply calculated field into workbooks with the following formatting:

 #.##M; #.##K

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.

IF SUM([Value]) > 1000000000 THEN LEFT(STR(SUM([Value])/1000000000),FIND(STR(SUM([Value]/1000000000)),".")+2)+ 'B'
ELSEIF SUM([Value]) > 1000000 THEN LEFT(STR(SUM([Value])/1000000),FIND(STR(SUM([Value]/1000000)),".")+2)+ 'M'
ELSEIF SUM([Value]) > 1000 THEN LEFT(STR(SUM([Value])/1000),FIND(STR(SUM([Value]/1000)),".")+2)+ 'K'
ELSE STR(SUM([Value]))
END

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.

comments powered by Disqus