Menu
    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?    
    PRINT THIS PAGE
    Related Links
    Format Numbers and Null Values
    Adjustment of Number Format Appears to Round Numbers Incorrectly
    Attachments
    multiple number formats example.twbx

    All Support

    SUPPORT BY PRODUCT
    TOOLS & DOWNLOADS
    INFORMATION
    ASSISTANCE