Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
10 months ago
Solved

Format string showing negative sign (-), sometimes.

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?

 

Need Help
Show and Tell
Tutorial Requests
  • OwenAuger's avatar
    OwenAuger
    10 months ago

    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.

     

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    10 months ago

    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.