Conditional formatting background colour of a table field

[DebbieE]

I have the following tables and columns to use for this

And I want to conditionally colour the Table Field based on the Fact and the Lookup %

So for example

Group                 Fact              Lookup %

Group1               79.1%          85%

If Fact The Same or Greater than Lookup % Then Green

If Fact 10% Under     Lookup % Then Amber

If Fact Greater that 10% Under Lookup % Then Red

But I don’t think I can do it . All I have is the following

Is there any way of setting up formatting against another data item in the way specified above?

[parry2k · Accepted solution]

@DebbieE it is pretty easy, actually you can create a measure to define the logic for the color and then use that in conditional formatting by selecting field value from drop down

something like, so you can put your logic in following measure and use this My Background Color for conditional formatting.

My Background color =
SWITCH( TRUE()
    Measure A > Measure B, "Green",
    Measure A = Measure B, "Yellow",
  "Red"
)

[parry2k]

@DebbieE you have percent option select in first set of condition, change it to number.

[DebbieE]

That was just something I did quickly to show that I cant format against another field in a table. 

[parry2k · Accepted solution]

@DebbieE it is pretty easy, actually you can create a measure to define the logic for the color and then use that in conditional formatting by selecting field value from drop down

something like, so you can put your logic in following measure and use this My Background Color for conditional formatting.

My Background color =
SWITCH( TRUE()
    Measure A > Measure B, "Green",
    Measure A = Measure B, "Yellow",
  "Red"
)

[DebbieE]

I started to try and create that measure (Its definitely a measure)

But it doesnt allow me to select my Average Measure that is already set up as a measure 

[parry2k]

@DebbieE hmmm, very hard to say why, if you adding this as a measure and you already have anotehr measure, you should able to refer existing measure. no idea how to debug without actually looking at it.

[DebbieE · Accepted solution]

I need to use a between I think and I dont think you can use BETWEEN in a Switch.

I think I may have some working DAX

My Background color =

IF([Avg Fact]>=AVERAGE('lookup SurveyGoals'[Goal]),"Green" ,

IF([Avg Fact]<AVERAGE('lookup SurveyGoals'[Goal]) && [Avg Fact]>= AVERAGE('lookup SurveyGoals'[Goal - 10]),"Yellow",

"Red"

))

Just to try and add it to my formatting now. Fingers crossed

[parry2k]

@DebbieE  IF condition is also fine but I like to use Switch it is easy to maintain and readbility point of view. Yes between can be used using and , anyhow let's if you get thru IF and then you have the solution.

[DebbieE]

Its all working,

 Thank you so much