KNOWLEDGE BASE

Custom Format Discrete Values as Symbols


Published: 13 Dec 2014
Last Modified Date: 25 Oct 2022

Question

How to show an asterisk for a specified discrete value and keep the integer formatting on the other values.

Environment

Tableau Desktop

Answer

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

  1. Create a calculated field.
  2. 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.
  3. Drag Category and Item to the Rows shelf.
  4. Drag New Sales onto Text in the Marks card. The table should show values of -1 where the original values were less than 500.
  5. Right-click AGG(New Sales) in the Marks card. Select "Format."
  6. In the "Pane" tab of the "Format" bar, select the drop down menu for "Numbers"
  7. 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.


 
Did this article resolve the issue?