Forum Discussion

andrea100's avatar
andrea100
Helper I
1 year ago

Implementing conditional formatting colouring with a sum of measures

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.

For some background, these have been my previous posts on this project:

  1. https://community.fabric.microsoft.com/t5/Desktop/Conditional-formatting-on-bar-chart-for-each-bar-Is-this/m-p/4257232#M1337717
  2. https://community.fabric.microsoft.com/t5/Desktop/Problems-implementing-conditional-formatting-on-bar-chart-with/m-p/4302998#M1349471

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. 

 

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

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    andrea100 , if this is % column, you need use value below 1 and change the order

     

    	
Color =
SWITCH(
    TRUE(),
	[Meets%]+[Exceeds%]>.90,"orange",
	[Marginal%]+[Below%]>.40,"yellow",
	[Exceeds%].>30,"green",
    [Below%]>.15,"red"  
)

     

    • andrea100's avatar
      andrea100
      Helper I
      1 year ago

      Hi amitchandak thank you for looking into this. I appreciate it. I've tried but it doesn't change the colouring.
      I also need to make an edit in my post of the conditional formatting that I'm using:

       

       

      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")

       

       

      So that the appropriate bar is coloured...Also no it is not a % column so I've kept the numbers as is.