Forum Discussion
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 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
10 Replies
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/Thanks Vivek,
I will try this and get back. Hoping its not too tedious as i have multiple variance variables
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.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
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
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 , the last solution is the one you are looking for?
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)
