KNOWLEDGE BASE

## Displaying Different Number Unit Abbreviations Based on Number Size

Published: 24 Mar 2016
Last Modified Date: 19 Jan 2021

### 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.

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.

4. 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

Discuss this article...
Did this article resolve the issue?