Conditional formatting on columns from different tables

[Anonymous]

Hi All

I have two tables with A and B which was created from sqlsever.  Table A has Actual Sales and Table B has Target Sales. Both Tables are related based on a Column WeekNumber . I want to perform conditional formatting on Actuals sales column (TableA) by highlighting the Actual Sales in Green Color if the Target Sales (TableB) is achieved for the corresponding week and city, and if not, then Red color.

What conditional formatting rule I have to apply ?

Thanks!

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you want to set the conditional formatting of [Actual sales] according to the comparison of data, you can try my steps:

1. Create a measure [Color]:

Color =

var _target=

CALCULATE(MAX('Table B'[Target sales]),FILTER('Table B',[City]=MAX('Table A'[City])&&[Week]=MAX('Table A'[Week])))

return

IF(MAX('Table A'[Actual sales])>=_target,"Green","Red")

2. Click on the table chart of ‘Table A’, “Conditional formatting”->advanced control, then set like this:

3. Change the font color of [Actual sales] to white, and you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.