Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
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?
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.
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.
Source Data and total employee calculation
Team Absent %
Overall Company Absent% calculation
Absent% Comparison Calculation
Conditional format setting and visualization
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?
User | Count |
---|---|
17 | |
17 | |
14 | |
13 | |
13 |
User | Count |
---|---|
17 | |
14 | |
13 | |
10 | |
8 |