Forum Discussion
Creating Conditional color format based on a changing measure
Hello again,
I am trying to change the background color of a measure column based on how close it is to a different, calculated measure that changes daily.
TSL% or Straight-Line : The percentage measure that is being refferenced as the target.
%Obs or O: The percentage measure that is being color formated based on its proximity to TSL%
I want it to look like this:
Here is my input.
If O is less than TSL%-.05(so minus 5%) then red, else if O is greater than TSL-.05 and less than TSL+.05 then yellow, else if O is greater than or equal to TSL%+.05 then Green
This gives me an output of:
I have also been looking at using "color based off calculation" at the bottom of this link but am having issues there too.
Any help, helps. Thanks
nmyre - First, I would write your measure like this:
Color = VAR O = [%Ob] VAR TSL = [TSL%] RETURN SWITCH(TRUE(), O <= TSL - .05,1, O > TSL - .05 && O < TSL +.05,2, O <=TSL + .05,3, 4 )Then you would do this:
I attached an example PBIX below sig. Page 8, Table 8.
nmyre - Yes, so in the example I attached, my "Color" measure was called "Measure 5". I set the conditional formatting on "Measure 3", using "Measure 5" as the basis for my rules.
So, in your case, you want to click the down arrow in the Visuals area not on %ObC but on your other measure, %Ob, which you want the color on and choose Conditional Formatting | Background Color. Then choose Rules. Then choose your Color measure and then setup your rules.
Hope that clears things up.
5 Replies
nmyre - First, I would write your measure like this:
Color = VAR O = [%Ob] VAR TSL = [TSL%] RETURN SWITCH(TRUE(), O <= TSL - .05,1, O > TSL - .05 && O < TSL +.05,2, O <=TSL + .05,3, 4 )Then you would do this:
I attached an example PBIX below sig. Page 8, Table 8.
Thank you for your response. I am close but not quite there. I feel as if I am missing something simple. Here is my Measure:
Here is my output:
The colors are correct for the adjacent value, but what I am looking for is the percentages inside the color cells. Example:
Any Ideas?
nmyre - Yes, so in the example I attached, my "Color" measure was called "Measure 5". I set the conditional formatting on "Measure 3", using "Measure 5" as the basis for my rules.
So, in your case, you want to click the down arrow in the Visuals area not on %ObC but on your other measure, %Ob, which you want the color on and choose Conditional Formatting | Background Color. Then choose Rules. Then choose your Color measure and then setup your rules.
Hope that clears things up.
