KNOWLEDGE BASE

Custom Number Format Show Unexpected Number When the Value Is Between [-0.5 ~ 0]


Published: 13 Jul 2021
Last Modified Date: 04 Oct 2021

Issue

When using a custom number format, the negative numbers are shown as positive numbers.
For example, when showing the numbers in Million, the negative profit show as [+0M] in Tableau.
User-added image

Environment

  • Tableau Desktop

Resolution

Option 1

Please create a String field to show the custom Number format.

For example, the calculation could be as follows:
=============
IF SUM([Profit])/1000000>=0
THEN '+' + STR(ROUND(SUM([Profit])/1000000)) + 'M'
ELSEIF SUM([Profit])/1000000>-0.5
THEN '- 0M'
ELSE '- ' + STR(-ROUND(SUM([Profit])/1000000)) + 'M'
END
=============
User-added image

Please check the attached workbook for details.

Option 2

Cast the rounded number as a string and concatenate it with a sign test, something like:
   `IF [original field] > 0 THEN "▲ " ELSEIF [original field] < 0 THEN "▼ " ELSE "" END + STR([rounded field])`

Option 3 

Another method avoids string casting, which may be less performant: create a duplicate of the field and add it to the Text/Label shelf so there are two copies there, then break the custom formatting string in two so the first instance of the field displays the up arrow ▲ or down arrow ▼ according to the sign of the number, and the second instance just displays millions out to two decimal places. Since the copy with the arrows is not rounding, it does not lose the sign when the value rounds to zero.

Lastly, ensure the two number fields display on the same line. Click the Text button on the Marks card, then the "..." button, and manually move the field references to the same line (just like editing a tooltip).
Please see the attached sample workbook for a demonstration.

Cause

Tableau rounds the numbers first and set the custom format.
So the value between "-0.5~0" is converted to "0" and then the [+0M] is shown in the view.
Did this article resolve the issue?