KNOWLEDGE BASE

Displaying Different Number Unit Abbreviations Based on Number Size


Published: 24 Mar 2016
Last Modified Date: 27 Feb 2018

Question

How to abbreviate data units for billions (B or G), millions (M), thousands (k), or none for one specific measure based on the size of the number. 

 

Environment

Tableau Desktop

Answer

Both options are demonstrated in the attached workbook using the sample data set World Indicators
CLICK TO EXPAND SOLUTION
Option 1: Create multiple calculations that can each be formatted separately
Note: this option will not work with views that include multiple measures

Step 1: Create Calculated Fields 

  1. Drag [Population Total] to the Columns shelf
  2. Drag [Country] to the Rows shelf
  3. Create a calculated field with a name like Population below 1K, enter the following formula, then click OK:
    IF AVG([Population Total]) < 1000
    THEN AVG([Population Total])
    END
      
  4. Create a calculated field with a name like "Population below 1M" with a calculation similar to the following:
     
    IF AVG([Population Total]) >= 1000
    AND AVG([Population Total]) < 1000000
    THEN AVG([Population Total])
    END
  5. Create a calculated field with a name like "Population below 1B" with a calculation similar to the following:

    IF AVG([Population Total]) >= 1000000
    AND AVG([Population Total]) < 1000000000
    THEN AVG([Population Total])
    END

Step 2: Formatting

  1. Right-click [Population below 1K] in the data pane and select Default Properties > Number format…
  2. In the Default Number Format dialog, format the number as desired. This example uses Number (Custom) and no decimal places.
  3. Repeat steps 1 and 2 for [Population below 1M], [Population below 1B], and [Population above 1B].
  4. Drag [Population below 1K], [Population below 1M], [Population below 1B], and [Population above 1B] to Label on the Marks card.
  5. Click on Label on the Marks card and click the button
  6. In the Edit Label dialog, remove the paragraph breaks between <AGG(Population below 1K)><AGG(Population below 1M)><AGG(Population below 1B)><AGG(Population above 1B)> so that all fields are on the same line.
CLICK TO EXPAND SOLUTION
Option 2: Convert the numeric data into text and manually format it
Note: Converting numbers with decimal places into text values is not consistent across all databases. For more information see Adjustment of Number Format Appears to Round Numbers Incorrectly. Additionally, text (string) manipulation might cause performance issues.
  1. Drag [Population Total] to the Columns shelf
  2. Drag [Country] to the Rows shelf
  3. Create a calculated field with a name like "Population Label", enter the following formula, and then click OK:

    IF AVG([Population Total]) < 1000
    THEN STR(ROUND(AVG([Population Total]),0))
    ELSEIF AVG([Population Total]) < 1000000
    THEN STR(ROUND(AVG([Population Total])/1000,0)) + "K"
    ELSEIF AVG([Population Total]) < 1000000000
    THEN STR(ROUND(AVG([Population Total])/1000000,1)) + "M"
    ELSE STR(ROUND(AVG([Population Total])/1000000000,1)) + "B"
    END

    The ROUND() function will return a value with the given number of decimal places. The STR() function will convert a number into a text (string) value.

 
  • Drag [Population Label] to Label on the Marks card.

Additional Information

To voice your support for the inclusion of this feature in a future product release, add your vote to the following Community Idea: Smarter Number Abbreviations
Did this article resolve the issue?