Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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

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

jdbuchanan71_0-1655228497532.png

jdbuchanan71_1-1655228514984.png

 

Anonymous
Not applicable

thanks for the help!

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.