The Data School - Different Number Formats in a single Field

Normally Tableau allows the use of one number format per field. But with positive values, it is possible to use two different formats within one field. This can be useful when you display numbers over highly different ranges. Fig.1, taken from a Workout Wednesday Challenge, shows how this can be useful.

Figure 1: Different numbers require different formatting.

Here a single calculated field that contains all the numbers is used, but the format is still different within the different categories. “Bats/Home run” and “Hits/Game” use two digits after the comma, the other categories use zero.

This can be done by a little manipulation of the data. The field which generates the individual categories is shown in Fig.2.

Figure 2: Calculated field that creates the categories. The red squares show which categories are made to be negative.

The fields which should use additional digits are turned negative here. When you go to the formatting options, Tableau offers unique formatting possibilities for positive and negative values.

By selecting “Custom” in “Number Format” you get the possibility to differentiate between the negative and positive values. The format, in this case, should be:

#0;#0.00

The semicolon is separating the positive and negative values from each other. The dot indicates where the decimals begin (Note that this might be a comma for you, depending on the workbook locale). Since the positive values have no dot, there are no decimals displayed.