Issues with Conditional Formatting for calculated measures

[Anonymous]

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-40... 

Entity Name	Operating Suplus CY	Operating Surplus PY
FE56bane Gi367BC	3.3M	4.1M
FE56hampton367BC	-0.1M	1.4M
FE56ich Gir367BC	1.4M	-0.2M
FE56ich Gra367BC	-1.0M	0.4M
FE56oomba G367BC	-1.6M	0.2M
sbanema	2.0M	8.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

[v-easonf-msft · Accepted solution]

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

[amitchandak]

@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)