cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ukfan123
Helper I
Helper I

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 format a gradient for each row, with the highest % being green, middle % being yellow, and lowest being red.

 

Example, Home-flooring Row, 2021 green background of the cell showing 32.31%, Yellow in cell showing 31.17% and red in cell showing 31.01%.

 

Thanks so much!

 

Ukfan123_0-1655227385434.png

Ukfan123_1-1655227403196.png

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Ukfan123 

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.

jdbuchanan71_0-1655228497532.png

jdbuchanan71_1-1655228514984.png

 

View solution in original post

5 REPLIES 5
speedramps
Super User
Super User

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

jdbuchanan71
Super User
Super User

Each one of those is a new measure in your model.  You also want to make sure that the format of the measure is Text:

jdbuchanan71_0-1655228758722.png

 

jdbuchanan71
Super User
Super User

@Ukfan123 

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.

jdbuchanan71_0-1655228497532.png

jdbuchanan71_1-1655228514984.png

 

thanks for the help!

That should work! Sorry I'm an idiot, can you tell me where to input the formulas please?

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors