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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Amy-Marriott
Frequent Visitor

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

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!

6 REPLIES 6
Dangar332
Super User
Super User

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.

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?

AmyMarriott_0-1722504602411.png

 

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
Super User
Super User

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.

Rakesh1705_0-1722414770291.png

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



Rakesh1705
Super User
Super User

Source Data and total employee calculation

Rakesh1705_0-1722362168650.png

Team Absent %

Rakesh1705_1-1722362205059.png

Overall Company Absent% calculation

Rakesh1705_2-1722362239946.png

Absent% Comparison Calculation

Rakesh1705_3-1722362271702.png

Conditional format setting and visualization

Rakesh1705_4-1722362324457.png

 

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?

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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