How to make Rev go is good and Exp go up is bad ?

[admin11]

Hi All

i have Table which display Rev & Exp in one table .

I can make Rev go up display VARI % green 

but I cannot make Exp go up display VARI % Red

See image below 

https://www.dropbox.com/s/0069e5bwamsh93k/IMG_5663.PNG?dl=0

Paul

[DataZoe · Accepted solution]

@admin11 I would try creating another measure to use for the conditional formatting:

CF for Table =

if(selectedvalue('Table'[ITEM])="Rev",[Vari NP %],
if(selectedvalue('Table'[ITEM])="COGS",[Vari NP %]*-1,

blank()))

then in the conditional formatting use this one as your range with the diverging option on, making the center 0.

[v-alq-msft · Accepted solution]

Hi, @admin11 

Based on your descritpion, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Measure1:

Measure1 = SUM('Table'[Value1])

Measure2:

Measure2 = SUM('Table'[Value2])

Vari %:

Vari % = 
DIVIDE(
    [Measure2]-[Measure1],
    [Measure1]
)

You may create a measure as below.

Color Control = 
var val = 
SWITCH(
    SELECTEDVALUE('Table'[Item]),
    "Rev",[Measure2]-[Measure1],
    "Exp",[Measure1]-[Measure2]
)
return 
IF(
    val>0,
    "green",
    IF(
        val<0,
        "red"
    )
)

Then you need to conditionally format 'Vari %' based on 'Color Control'.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin11]

@v-alq-msft 

Thank you very much

[DataZoe · Accepted solution]

@admin11 I would try creating another measure to use for the conditional formatting:

CF for Table =

if(selectedvalue('Table'[ITEM])="Rev",[Vari NP %],
if(selectedvalue('Table'[ITEM])="COGS",[Vari NP %]*-1,

blank()))

then in the conditional formatting use this one as your range with the diverging option on, making the center 0.

[PhilipTreacy]

@admin11 

Hi Paul,

Can you link to your PBIX please.

Regards

Phil

[admin11]

@PhilipTreacy 

Thank you for sharing alway.

https://www.dropbox.com/s/n8pkchrae5x09jf/PBT_V2021_339%20ask%20how%20to%20make%20expense%20blackgro...

Above link is my PBI file

Paul