Forum Discussion

NOVICE02's avatar
NOVICE02
Helper III
6 years ago

Conditional formatting based on another table

I cannot figure this thing out and i feel that it is too simple. I have data that I direct query and have put into a table to look at month to date values by different metrics. I have created a targ...
Need Help
condolar's avatar
condolar
Helper I
3 years ago

Hi vivran22 ,

 

Thank you for this solution, it is exactly what I have been looking for!!

 

I have one futher question, is it possible to add a third colour to this? I wish to format the cells with the rules

 

Actual >= Target

Actual < Target

Actual <0.7*Target

 

In a Red Amber Green format. Your solution works for two colours, I did try adding another rule but it didn't work. Could you help?

 

Thanks,

Connor

condolar's avatar
condolar
Helper I
3 years ago

Actually, I think I have found a solution! I use the following to acheive what I wanted:

 

NotebookCF = 
SWITCH(
    TRUE (),
    ISBLANK ( [Notebooks Target] ), "#C0C0C0",
    //met or exceeded
    [Notebooks Created] >= [Notebooks Target], "#85e085",
    //more than 70% there
    {[Notebooks Created] < [Notebooks Target]&&[Notebooks Created]>0.7*[Notebooks Target]}, "#eead0e",
    //less than 70% there
    [Notebooks Created] < 0.7*[Notebooks Target], "#f08080"
)