topic Re: Issues conditional formatting coloring with a sum of measures in DAX Commands and Tips

Issues conditional formatting coloring with a sum of measures

andrea100 — Fri, 24 Jan 2025 19:43:28 GMT

Hi community, I have been working on this visual for some time now. I’ve had members help me along the way which has saved me greatly. I am having issues with implementing conditional formatting on a bar chart depending on measures - I have been trying to create a type of bar chart where each bar may be coloured depending on measures.

This is how the bar chart looks like:

I have used this for conditional formatting:

Conditional Formatting Measure = Switch(TRUE(), SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Meets " && [Meets%]+[Exceeds%] > 90, "orange", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Marginal" && [Marginal%] + [Below%] > 40, "yellow", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Exceeds" && [Exceeds%] > 30, "green", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Below" && [Below%] > 15, "red", "grey")

When there is a sum used in the condition, the bar does not change colour – the sum is not taken into account. I have many instances where Marginal+Below>40 and Meets+Exceeds>90 but the bar doesn't change colour.

Here is a link to a sample workbook: google drive

I would really appreciate any help and guidance on this. I don't understand why the sum is being ignored.

Re: Issues conditional formatting coloring with a sum of measures

Fowmy — Sat, 25 Jan 2025 06:03:56 GMT

andrea100

Your works, i changed the values and tested, it's fine. Please refresh your model and test

Re: Issues conditional formatting coloring with a sum of measures

andrea100 — Mon, 27 Jan 2025 17:55:19 GMT

Fowmy Yes I do see now that when the values are changed it works. Any idea why it does not colour in this case or for these values? What am I missing?

In this case, Exceeds+Meets>90, but why does it not highlight?

Re: Issues conditional formatting coloring with a sum of measures

Fowmy — Mon, 27 Jan 2025 18:00:50 GMT

andrea100

Becasue it does not go above 90, Exceeds = 0 and Meets = 53.61, total is 53.61

Re: Issues conditional formatting coloring with a sum of measures

andrea100 — Mon, 27 Jan 2025 18:06:11 GMT

Fowmy Thank you very much for looking into this by the way.
I'm wondering why is it calculating so that Exceeds = 0, when actually, Exceeds = 41.47
I am looking for the bar Meets to be coloured since Exceeds = 41.47 and Meets = 53.61, where the sum is 95.08 (greater than 90).

Re: Issues conditional formatting coloring with a sum of measures

Fowmy — Mon, 27 Jan 2025 18:11:46 GMT

andrea100

Please check your Measure [Exceeds], as per table visual below the column chart, it shows zero.

Re: Issues conditional formatting coloring with a sum of measures

andrea100 — Mon, 27 Jan 2025 18:55:08 GMT

Fowmy Okay I see what you mean now. My intent of creating the measures [Exceeds%], [Meets%] and so on, was to capture the % total of each performance level as I wasn't able to figure another way to capture it. I see it has caused another layer of problems.

Re: Issues conditional formatting coloring with a sum of measures

Fowmy — Mon, 27 Jan 2025 19:02:31 GMT

andrea100

Hope I was able to clarify the issue based on what you presented. I hope you will be able to look into the issues with your measres.