Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Issues with Conditional Formatting for calculated measures

Hi friends,

I tried but couldn't figure it out, I have this calculation below which i would like to

1. add the "+" sign for positive variance. so made the change (in red) for my formula and it returns error as it couldn't display.

2. be able to condition format the number , Green if it is positive surplus and red if it is negative.

 

https://app.powerbi.com/groups/me/reports/afde82bd-bc8f-4d2b-9762-afca05016032?ctid=4e3b95cb-ee21-4049-9604-bd201ffbcf8b&pbi_source=linkShare  

Entity NameOperating Suplus CYOperating Surplus PY
FE56bane Gi367BC3.3M4.1M
FE56hampton367BC-0.1M1.4M
FE56ich Gir367BC1.4M-0.2M
FE56ich Gra367BC-1.0M0.4M
FE56oomba G367BC-1.6M0.2M
sbanema2.0M8.3M

 

Operating Surplus_CY =

VAR OP_CY =

FORMAT(

CALCULATE(

SUM(Sheet1[Value]),

'End Year'[End Year]

),

"#,##0,.0M")

 

Return

if(OP_CY > 0, "+"&OP_CY,OP_CY")

 

Any kind help is much appreciated

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

     

    Operating Surplus_CY =

    VAR OP_CY =
    CALCULATE(
    SUM(Sheet1[Value]),
    'End Year'[End Year]
    )

    var _2 = format(OP_CY,"#,##0,.0M")

     

    Return

    if(OP_CY > 0, "+"& _2, _2)

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, Anonymous 

    Please try formula as below:

    calculated column:

    Operating Surplus_CY =
FORMAT (
    CALCULATE ( SUM ( Sheet1[Value] ), 'End Year'[End Year] ),
    "+#,##0,.0M;-#,##0,.0M"
)

    measure (conditional formatting)

    Conditional fomatting =
IF (
    [Operating Suplus CY] > 0,
    "lightgreen",
    [Operating Suplus CY] < 0, "red" )
)

     

    related document:

    Custom numeric formats 

     

    Best Regards,
    Community Support Team _ Eason