DYNAMIC Conditional Formatting based on the values of other fields?

[Christofor]

Hi All-

I am looking to DYNAMICALLY conditionally format the values in a multi-row card based on their relationship to a target field.

I have NumberCompleted and TargetCompleted measures, and I'm looking for format the NumberCompleted measure in the card depending on its relationship to the Target Completed measure. The Target Completed and the number completed change daily, so I don't want to hard code in values.

Basically, IF (NumberCompleted >= (.9*Target Complete)), Green, 

               IF (NumberCompleted >= (.7*Target Complete)), Yellow, 

               IF (NumberCompleted <= (.7*Target Complete)), Red

I can't figure out a way to do this while maintaining values- it seems like reducing these to percentages would work because I could hard code in <70, 70-90, etc, but the value is preferred, and it's a situation that I've come across numerous times when I'd like to format based on things like historical averages for a field, etc. 

Any ideas?

[jdbuchanan71 · Accepted solution]

@Christofor 

You can write a measure to use to apply the formatting.  Assuming your [Number Complete] and [Target Complete] are both measures, it would look like this.

Formatting = 
VAR _Result = DIVIDE ( [Number Completed], [Target Completed] )
RETURN
SWITCH(
    TRUE(),
    _Result >= .9, "Green",
    _Result >= .7, "Yellow",
    _Result < .7, "Red"
)

Make sure to set the format of the [Formatting] measure to Text.  I have applied it as the background color conditional formatting to the row field:

[Christofor]

Thank you so much! This has unlocked a whole new level for me!

[Christofor]

Thanks so much! This has unlocked a whole new level for me.