Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@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.
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”)
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:
@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.
thanks for the help!
That should work! Sorry I'm an idiot, can you tell me where to input the formulas please?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |