Truncate a number in an arbitrary unit

Published: 28 Nov 2022
Last Modified Date: 28 Nov 2022

Question

How to truncate a number in arbitrary unit as shown below.

If you select "thousand (K) ":
User-added image

If you select "Million (M) ":
User-added image

If you select "Billion (B) ":
User-added image

Environment

Tableau Desktop

Answer

1. Connect to attached data source in Tableau Desktop.
2. Create a new parameter as demonstrated in the following screen capture:
User-added image

3. Right-click on Truncate Unit and select Show Parameter.
4. Create a new calculated field like the following:
Name : Sales (Truncated)
Calculation : INT(SUM([Sales])
-
INT(RIGHT(STR(FLOOR(SUM([Sales]))),[Truncate Unit])))
5. Create a new calculated field with the following:
Name : Sales (K/M/B)
Calculation : Case [Truncate Unit]
WHEN 3
THEN REPLACE(STR([Sales (Truncated)]),"0","") + "K"
WHEN 6
THEN REPLACE(STR([Sales (Truncated)]),"0","") + "M"
WHEN 9
THEN REPLACE(STR([Sales (Truncated)]),"0","") + "B"
END
6. Drag Sales (K/M/B) to Text.