top of page

Formula to Automatically display T, B, M or K!

Updated: Apr 27

You may not be aware that you can change the way your numbers are displayed in Tableau on the Format pane under Display Units.



This will allow you to display thousands as 1K instead of 1000.


But what if you want your label to automatically abbreviate AND display T or B or even K at the end of your number?


I found this formula a couple of years ago from fellow Tableau Social Ambassador Will Perkins.


Save the following calculated field somewhere!!!


Sales Label

IFNULL('$'+

//Trillions Piece

IF ABS(SUM([Sales])) > 1000000000000 Then

STR (ROUND((SUM([Sales]))/1000000000000,1))+'T'

//Billions Piece

ELSEIF ABS(SUM([Sales])) > 1000000000 Then

STR(ROUND((SUM([Sales]))/1000000000,1))+'B'

//Millions Piece

ELSEIF ABS(SUM([Sales])) > 1000000 Then

STR(ROUND((SUM([Sales]))/1000000,1))+'M'

//Thousands Piece

ELSEIF ABS(SUM([Sales])) > 1000 Then

STR(ROUND((SUM([Sales]))/1000,1))+'K'

//All Others

ELSE

STR(ROUND(SUM([Sales])/1))

END,'N/A')


Here's a view of what it does;



Just replace [Sales] with the measure you want to automatically to abbreviate.


If you don't want the dollar sign in the front, just remove "'$'+" after the ISNULL( in the formula.


What if you don't want the decimal place? Simply change the 1 in all of the ROUND formulas at the end.


So instead of STR(ROUND((SUM([Sales]))/1000000,1))+'M'

change it to STR(ROUND((SUM([Sales]))/1000000,0))+'M'


It will appear as below;


You can also test this label by filtering your data. I filtered my data by year, month and even week to see the label automatically change.


Will Perkins has updated this formula since he shared it with me years ago! see the below code from his X post:


//Define if the number is negative or not

IF SUM([Value]) <0 THEN '(' ELSE '' END +

//If you have a field with multiple number types, this helps identify a prefix

//IIF (CONTAINS(ATTR([Number Type]), '$'), '$', '')

//Otherwise, you can just comment the above row out and define it however it should be

//'$' +

IFNULL(

STR(INT(ROUND(ABS(SUM([Value]))/POWER(1000, INT(LOG(ABS(SUM([Value])),1000))), 1))) +

'.' + RIGHT(STR(ABS(ZN(ROUND(ROUND(ABS(SUM([Value]))/POWER(1000, INT(LOG(ABS(SUM([Value])),1000))), 1)*10, 0)))),1)

, '-') +

//Determine the Unit to display based on the Log Base 1000

CASE INT(LOG(ROUND(ABS((ZN(SUM([Value]))))/10, 1)*10,1000)) //Unit Symbol

WHEN 1 THEN 'K'

WHEN 2 THEN 'M'

WHEN 3 THEN 'B'

WHEN 4 THEN 'T'

ELSE ''

END +

//Close the parenthesis

IF SUM([Value]) <0 THEN ')' ELSE '' END

Comments


bottom of page