Conditional formatting using controls

[yodha]

Hi, am new to power bi, i need help to write a measure for conditioanl formatting, i want to change the colors using controls.

need to apply conditional formatting for "%C" column, where values lessthan 50% should be RED in color (back ground color), 51 % to 90% should be YELLOW in color, and anything greater than 90 % should be GREEN in color.

Attched sample PBIX for referrence:

https://www.dropbox.com/s/dqt3ggv9zpikl0r/Sample%20report.pbix?dl=0

Thanks in advance

[parry2k]

@yodha here you go

[parry2k]

@sanimesa your solution will work but if ever there is % more than 100% which can happen, it will fail. Just a thought.

Cheers!!

[sanimesa]

@parry2kGreat! Thanks for the tip!

[yodha]

@parry2k , @sanimesa : Thanks for your response.

but these "%" values are not constant 

(lessthan 50% should be RED in color (back ground color),

51 % to 90% should be YELLOW in color,

and anything greater than 90 % should be GREEN in color). 
These values may change in future.. So for that reason i want to use controls like this, and don't want to use the already existing conditional formatting feature.

Thank you,

[sanimesa · Accepted solution]

@yodhaCreate a measure like this and then use this measure value to conditionally format. You really only need two thresholds - one for red - yellow boundary and the other yellow - red. In any case, you can easily rejig your report to fit this.

Measure 3 = 
VAR Avalue = sum('Table'[A])
VAR Bvalue = sum('Table'[B])
VAR pctvalue = DIVIDE(Bvalue, Avalue)*100

RETURN SWITCH(TRUE(),
AND(pctValue > 0, pctvalue <= Red[Red Value]), 0,
AND(pctvalue > Red[Red Value], pctvalue <= Yellow[Yellow Value]), 1,
AND(pctvalue > Yellow[Yellow Value], pctvalue <= Green[Green Value]), 2,
3)

 

[parry2k]

@yodha how about to have color in the condition so that it is easy to change and conditional formatting just looking at the field value for the color rather than further conditions in conditional formatting, it is kind of two conditional stuff going on.

Measure 3 = 

RETURN SWITCH(TRUE(),
[Your % Measure] >= [Green Value], "Green",
[Your % Measure] >= [Yellow Value], "Yellow",
"Red"
)

you can change the above conditions to meet your business logic, and instead of a color name, you can add Hex color code. Now in conditional formatting, choose Field Value

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@sanimesa as a matter of fact, if it is less than 0% in other words, if it is negative %, it will fail there too. 

[sanimesa]

@yodhaYou can easily achieve this by choosing conditional formatting on the %C column as shown in the diagram below. Unless there is something else you want - like different shades of green etc.