Conditional formatting based on another column

[Anonymous]

I would like to apply conditional formatting to matrix values based on a column that is not used in the matrix. This link shows how to do it: https://docs.microsoft.com/en-us/business-applications-release-notes/april18/power-bi/desktop/condit...

When I right click on a field and select conditional formatting, the options I have are 'Background color' and 'Font color' whereas all other posts and examples I have seen have 'Background color scales' and 'Font color scales. Within the 'Background color' options I am not able to apply the formatting based on another column as per the link.

Version 2.64.5285.582 64-bit (November 2018) 

[Anonymous]

I would like to get to this:

[v-yulgu-msft]

Hi @Anonymous,

---

---

Above screenshot you provided might be from the old version of Power BI desktop. It returns the same result when you set conditional format like below using November 2018 release. It looks like the latest version just add a new option "Defaul formatting"

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana Gu, 

I'm using the PowerBi version 2.68.5432.761 64-bit (April 2019) and am still getting the same issue of the screenshot from the orginal question, of not being able to base the formatting on another column. 

I've tried your solution and it doesn't work with what I am aiming to do. 

My aim is to compare previous month data to the recent month data, and highlight the recent month data in red or green depending on if it is higher or lower than the previous month. 

Please can you advice?

Kind Regards,

Jess 

[Anonymous]

@Anonymous  were you able to achieve this? 

"to compare previous month data to the recent month data, and highlight the recent month data in red or green depending on if it is higher or lower than the previous month"

I am looking to do the same but dont have solution yet. 

Thanks 

[Anonymous]

Hi @Anonymous 

I had a similar requirement to you and I have found a solution.

Create a new measure using an IF statement to return 1 or 0 if column A is bigger than column B. In my example I first used a sumx measure to produce column totals to use in my table.

new measure = IF ([columnA]>[columnB], 1, 0)

If you put this new measure in to your column you will see a 1 or 0 depending on if the column value is bigger or not. While you don't need to leave this in your final version, it is helpful to check the measure worked as intended.

You can now set a conditional format to change the column format dependent on the results of your new measure. In my example I wanted to see if the actual value is higher than budget, colour red if yes and colour green if no. I set the conditional formatting as per my screenshot. Select the new measure in the "Based on filed" option, and select your desired colours.

I hope this helps!

[Anonymous]

Frustrating, I know. 

I have multiple columns that I need to apply the same conditional formatting to, and I need to go one by one over and over again applying the same rules...

It was so simple and fast in Excel.

Here it's just wasting your time.