Creating Conditional color format based on a changing measure

[nmyre]

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

[Greg_Deckler · Accepted solution]

@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.

[Greg_Deckler · Accepted solution]

@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]

@Greg_Deckler 

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?

[Greg_Deckler · Accepted solution]

@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.

[nmyre]

And there it is! Very Good. Thank you.

[Greg_Deckler]

@nmyre - Awesome! Yeah it's all easy once you know the trick!! But getting there is not necessarily super intuitive! 🙂