Last Modified Date: 19 Jan 2021
Environment
Tableau DesktopAnswer
Both options are demonstrated in the attached workbook using the sample data set World IndicatorsCLICK 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
- Drag [Population Total] to the Columns shelf
- Drag [Country] to the Rows shelf
- 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 - 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 - 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
- Right-click [Population below 1K] in the data pane and select Default Properties > Number format…
- In the Default Number Format dialog, format the number as desired. This example uses Number (Custom) and no decimal places.
- Repeat steps 1 and 2 for [Population below 1M], [Population below 1B], and [Population above 1B].
- Drag [Population below 1K], [Population below 1M], [Population below 1B], and [Population above 1B] to Label on the Marks card.
- Click on Label on the Marks card and click the … button
- 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.
- Drag [Population Total] to the Columns shelf
- Drag [Country] to the Rows shelf
- 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"
ENDThe 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 AbbreviationsDiscuss this article... Feedback Forum
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials