Conditional Formatting Not Working

[calen]

Hello All, 

I have each customer ranked by sales for the last 12 months. I also have their rank last year based on sales for the 12 months before that. I want to add conditonal formatting, if their rank went up - show green, if their rank went down - show red. I created the measure below: 

Rank Color = IF([Rank]>[Rank LY], 1, IF([Rank]<[Rank LY], -1, 0))

Then I formatting the Rank and Rank LY columns to show red a green. 

Here are the results I get based off of that formula and formatting. 

As you can see, my IF statement is not working. Does anyone know how I can fix my IF statement or get the conditonal formatting to work with another approach? 

[sanimesa]

@calenYou probably created a column called Rank Color and not a measure, right? I tried your exact scenario and strangely your exact formula works. Not sure why it is producing the incorrect values for you. Would it be possible for you to upload the PBIX?

[v-alq-msft]

Hi, @calen 

Based on your description, I created data to reproduce your scenario.

You may create a measure as below.

Rank Color = 
var _rank = SELECTEDVALUE('Table'[Rank])
var _rankly = SELECTEDVALUE('Table'[Rank LY])
return
IF(
    _rank>_rankly,
    1,
    IF(
        _rank<_rankly,
        -1,
        0
    )
)

Then you need to conditonal format 'Rank' and 'Rank LY' as below.

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.

[MFelix · Accepted solution]

Hi @calen ,

Try the following code:

Condittion = 
SWITCH (TRUE(),
[Rank]>[Rank LY], 1, 
[Rank]<[Rank LY], -1
, 0)