Format string showing negative sign (-), sometimes.

[RobRayborn]

I'm using calculation groups and one is to show the growth % form previous year.
I'm using the following Fromat String to format this calculation group: VAR Output =IF( SELECTEDMEASURE() >= 0, FORMAT( SELECTEDMEASURE(), "0%" ), UNICHAR(128315) & FORMAT( SELECTEDMEASURE() , "0%; (0%)" ) )RETURN"""" & Output

The format string shows the positive percentage normally, ex: 20%.  When the percentage is negative there will be a red downward pointing triange then (20%), unless the negative amount is less than negative (50%).  It appears that any percentage less than (50%) the negative symbol of "-" shows up infront of the red downward triangle.
I would like to keep it consistant. If one negative amount has the "-" they all should, or they all should not.
Can the Format String be rewritten to correct this, and/or can someone explain to me why this is happening?

[OwenAuger · Accepted solution]

Hi @RobRayborn 

First off, to fix the issue I would suggest this format string which should give you the intended format:

"0%;" & UNICHAR ( 128315 ) & "(0%);0%"

To explain the issue:

1. In general, a format string expression should return the format string itself, not the formatted measure value as text. (In some cases it can be useful to do this but is not needed here).
2. If you did want to return a literal formatted value, you would need to return """" & Output """"
3. SELECTEDMEASURE () is a reference to the underlying measure used in the visual, which is not the Growth % returned by this calculation item's expression but is rather the original measure for which you are calculating Growth %. So the logic is not correct for testing the sign of the Growth % value.

[RobRayborn · Accepted solution]

Thank you. That worked perfectly.

[OwenAuger · Accepted solution]

Hi @RobRayborn 

First off, to fix the issue I would suggest this format string which should give you the intended format:

"0%;" & UNICHAR ( 128315 ) & "(0%);0%"

To explain the issue:

1. In general, a format string expression should return the format string itself, not the formatted measure value as text. (In some cases it can be useful to do this but is not needed here).
2. If you did want to return a literal formatted value, you would need to return """" & Output """"
3. SELECTEDMEASURE () is a reference to the underlying measure used in the visual, which is not the Growth % returned by this calculation item's expression but is rather the original measure for which you are calculating Growth %. So the logic is not correct for testing the sign of the Growth % value.