Conditional Formatting to show colour grades dependant on a changing measure outcome

[Amy-Marriott]

Hi there,

I have a measure that is dividing the number of absent employees over the past year by the average headcount providing me an overall % of absent vs headcount.

I have then added this % into a table and added in Teams where it then shows the % of absent employees by team.

What I would like to format is the colour of the Team level % by the overall measure outcome.

For example, lets say the companywide % of absent employees is 10%. Team X has a value of 5% in the table therefore can be coloured green as it is below the companywide overall number or 10%. Team Y has 20% so should be red. This format should change depending on the overall firm % so if it drops to 4% companywide, Team X would then turn red too.

How do I do this please? I have been trying for a few days now to no avail!

Thank you!

[Dangar332]

Hi, @Amy-Marriott 

Try below measure for conditional formating

Conditional_measure=
var a = calculate([% meausre],removefilter('table'[Team column]))
switch(true(),
ISINSCOPE('table'[Team column]) && [% meausre]<=a,"Green",
ISINSCOPE('table'[Team column]) && [% meausre]>=a,"red"
)

For know how to use measure as  conditional formatting refer LINK.
you can also refer Microsoft  documents for Conditional formatting LINK 

Best regards,
Dangar 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Amy-Marriott]

Thank you Dangar! The switch isn't working without an = before it and when this is placed in the variable isn't pulling through for the <= X. Is there something I'm missing here?

[Dangar332]

Hi, @Amy-Marriott 

Try below measure it gives error because it not find value for Columns

Conditional_measure=
var a = calculate([% meausre],removefilter('table'[Team column]))
switch(true(),
ISINSCOPE(min('table'[Team column])) && [% meausre]<=a,"Green",
ISINSCOPE(min('table'[Team column])) && [% meausre]>=a,"red"
)

Best regards,
Dangar 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Rakesh1705]

Hi @Amy-Marriott in any situation your percentages will not go beyond 100. Hence it is no harm to use this range.

Still as per your request i have given a try. In your situation what I am understanding is that if the comparison value become negative then it will be red and and if its positive then its green. So its better to take atleast 0 in the comparison.

If you think the logic is going with your requirement then please accept the same as your solution.

[Rakesh1705]

Source Data and total employee calculation

Team Absent %

Overall Company Absent% calculation

Absent% Comparison Calculation

Conditional format setting and visualization

[Amy-Marriott]

Hi Rakesh, really appreciate your response thank you!

I note though on the conditional formatting screenshot that you are still putting in a figure of 0 and 100. I need the conditional formatting to be flexible with the outcome of the companywide figure on an ongoing basis not static numbers. Is this possible?