Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with conditional formatting

Hello, for some reason I can't figure out how to type a formula for conditional formatting in this new version of Power BI.    I would love some help to figure that out, but to also conditional for...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Anonymous 

    The only way I could think to do it is with 3 formatting measures becuse you have 3 different compare points.

     

    2021 format = 
VAR _Field = [2021]
VAR _Compare1 = [YTD]
VAR _Compare2 = [Latest Month]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)
    YTD format = 
VAR _Field = [YTD]
VAR _Compare1 = [2021]
VAR _Compare2 = [Latest Month]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)
    Latest Month format = 
VAR _Field = [Latest Month]
VAR _Compare1 = [2021]
VAR _Compare2 = [YTD]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)

     

    Then you apply each to the corresponding field as the background color.

     

speedramps's avatar
speedramps
Super User
4 years ago

Hi Ukfan123

 

Thanks for your question.

 

You say "conditional format a gradient for each row" but then "cell".

I assume you need help with cell conditional formating not rows ....

 

Create these 3 measures.

Then right click on the fields, conditional formating, background colur, format style = field value,

what field should we base this on = the appropraite RAG measure.

 

I have helped you, now please help me by giving kudos.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each solver will get the kudos they deserve. Thank you ! 😎

 

RAG 2021 =
SWITCH( True(),
[2021] > YTD && [2021] > [Latest month], “Green”,
[2021] < YTD && [2021] < [Latest month], “Red”,
“Yellow”)

RAG YTD =
SWITCH( True(),
[YTD] > [2021] && [YTD] > [Latest month], “Green”,
[YTD] < [2021] && [YTD] < [Latest month], “Red”,
“Yellow”)

 

RAG Latest month =
SWITCH( True(),
[Latest month], > [2021] && [Latest month], > [YTD], “Green”,
[Latest month], < [2021] && [Latest month], < [YTD], “Red”,
“Yellow”)