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