Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditional formatting on columns from different tables

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!

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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")
    1. Click on the table chart of ‘Table A’, “Conditional formatting”->advanced control, then set like this:

     

    1. 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.

1 Reply

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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")
    1. Click on the table chart of ‘Table A’, “Conditional formatting”->advanced control, then set like this:

     

    1. 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.