KNOWLEDGE BASE

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.
Did this article resolve the issue?