Dynamic Number Formatting

Dynamic Number Formatting in Tableau

Recently I saw some examples of Dynamic Number Formatting on Twitter, so I though I'd share a solution I came up with a few years ago. 

This solution uses some simple math to determine the base 10 of a number. Using that base 10 we can determine how to round it, and which numeric symbol it should have. Solution also removes 0 decimals, and handles negatives.

Copy/Paste this calc and change [Value] to the field you want to have dynamically formatted. NOTE* we are aggregating in the calc, so if you want to dynamically display a value that is MAX/MIN/AVG/ETC, change the aggregation in the formula.

//Determine if the value is negative
IF SUM([Value]) < 0 THEN '-' ELSE '' END
+
'$' //Dolar Symbol
+
//Get Int Amount by Dividing the Value by its' Base 1000 Value
STR(INT(ROUND(ABS(SUM([Value]))/POWER(1000,INT(LOG(  ROUND(ABS(SUM([Value]))/10,0)*10 ,1000))),1)))
+
//Determine if you need to show a decimal
//If the Modulo is zero, we just want to show blank (Ex 159.0K = 159K)
//Else we want to show the decimal value (Ex 159.4K)
IF (ROUND(ABS(SUM([Value]))/POWER(1000,INT(LOG(ABS(SUM([Value])),1000))),1)*10)%10 = 0 THEN ''
ELSE '.' + STR((ROUND(ABS(SUM([Value]))/POWER(1000,INT(LOG(ABS(SUM([Value])),1000))),1)*10)%10)
END
+
//Determine the Unit to display based on the Log Base 1000
CASE INT(LOG(ROUND(ABS(SUM([Value]))/10,0)*10,1000)) //Unit Symbol
WHEN 1 THEN 'K'
WHEN 2 THEN 'M'
WHEN 3 THEN 'B'
WHEN 4 THEN 'T'
ELSE ''
END