Last Modified Date: 25 Oct 2022
Environment
Tableau DesktopAnswer
In Tableau Desktop, the Special Values formatting option is available for continuous values but not for discrete values. In a text table, to display certain discrete values as a given symbol we must first set the specified values as a negative integer in a calculated field. Once the specified values are made negative, we can format them separately from positive numbers using the custom format option in the Format pane -> Numbers drop down menu. The steps below detail an example situation using superstore data, in which the goal is to display an asterisk in a text table whenever [Sales] is less than a specified value (500).
- Create a calculated field.
- In the Formula box, enter the following:
IF sum([Sales]) < 500 THEN -1 ELSE (sum([Sales])) END
- Rather than replacing values less than 500 with asterisks right away, for now we will replace them with a value of -1.
- Note that this way we can keep sum(Sales) as an integer, so the values will retain the integer formatting.
- Drag Category and Item to the Rows shelf.
- Drag New Sales onto Text in the Marks card. The table should show values of -1 where the original values were less than 500.
- Right-click AGG(New Sales) in the Marks card. Select "Format."
- In the "Pane" tab of the "Format" bar, select the drop down menu for "Numbers"
- Select "Custom" at the bottom and insert the following in the box:
#,##0.0;"*"
Anything entered after the ";" affects only the formatting of the negative numbers. Thus, when we write "*", we are telling Tableau to place the string "*" where a negative number appears in the table. Since we changed all the values less than 500 to -1, all of these values now appear as an asterisk.
Additional Information
To view the above steps in action, see the video below.Note: the video has no sound. To view the video in higher quality, click the YouTube icon below to watch it on YouTube directly.