Conditional formatting based on another table

[NOVICE02]

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 target table in excel and bring it into power bi and i then calculated a measure table to calculate the variance. What i want to do is colour code the variance metrics based on the target table. When i go into conditional formatting it allows me to choose the field tht i want to do the comparison against but it doesn't load anything. Am i doing something wrong ?

Column 1 is MTD actuals, column 2 is target and column 3 is variance. they are 3 different tables

[amitchandak]

@NOVICE02 , the last solution is the one you are looking for?

[NOVICE02]

Is there an alternate solution to creating new measures? I am worried that one day when someone inherits this process/table it's going to be hell for them to decode(just in case)

[vivran22]

Hello @NOVICE02 

Conditional formatting sometimes is tricky in Power BI, especially when you want to involve multiple tables.

There could be multiple ways of doing it, but the way I do it is by creating a measure for defining the color:

Color AHT = 
SWITCH (
    TRUE (),
    ISBLANK ( [tgt AHT] ), "#C0C0C0",
    [Avg Handle Time] <= [tgt AHT], "#008000",
    [Avg Handle Time] > [tgt AHT], "#f08080"
)

In the example above, I am comparing two measures: [Avg Handle Time] against [Aht target] which are in two separate tables.

Once this measure is created, apply the following steps in conditional formatting :

Format by > Field Value

Then select the measure created above:

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[condolar]

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]

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"
)

[NOVICE02]

it didn't work... I wrote a measure to test like this:

Live_pen =

SWITCH (

TRUE (),

    ISBLANK ( [% liv_pen_var] ), "#BBE4EC",

    [% liv_pen_var] < 0 , "#DA291C" ,[% liv_pen_var] = 0 ,"#FFBF3F",[% liv_pen_var] > 0, "#00B050")

and followed the same steps to apply formatting based on above field value but the value is unchanged.

[vivran22]

@NOVICE02 

I am not sure how you are applying it. I have used the same formula and it is working for me:

Can you share more details on this?

Cheers!
Vivek


If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂


https://www.vivran.in/

Connect on LinkedIn

[NOVICE02]

Once I write the measure.. I choose the able and select var. penetration... which is the measure defined in:

Live_pen =

SWITCH (

TRUE (),

    ISBLANK ( [% out_liv_pen_var] ), "#BBE4EC",

    [% out_liv_pen_var] < 0 , "#DA291C" ,[% out_liv_pen_var] = 0 ,"#FFBF3F",[% out_liv_pen_var] > 0, "#00B050")

From conditional formatting i select format by field value and choose the measure that i created and click ok...

and nothing... am i missing a step ?

[vivran22]

@NOVICE02 

You have followed all the steps, but still not getting the desired result. Is it possible to share the pbix file with the sample data?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[NOVICE02]

Thanks Vivek,

I will try this and get back. Hoping its not too tedious as i have multiple variance variables