topic Re: Conditional Formatting to show colour grades dependant on a changing measure outcome in DAX Commands and Tips

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

Amy-Marriott — Tue, 30 Jul 2024 16:27:09 GMT

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!

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

Anonymous — Tue, 30 Jul 2024 17:58:56 GMT

Source Data and total employee calculation

Team Absent %

Overall Company Absent% calculation

Absent% Comparison Calculation

Conditional format setting and visualization

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

Amy-Marriott — Wed, 31 Jul 2024 07:57:07 GMT

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?

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

Anonymous — Wed, 31 Jul 2024 08:33:44 GMT

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.

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

Dangar332 — Thu, 01 Aug 2024 07:31:27 GMT

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.

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

Amy-Marriott — Thu, 01 Aug 2024 09:30:16 GMT

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?

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

Dangar332 — Thu, 01 Aug 2024 10:54:03 GMT

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.