Ho to compare two rows, and color the cell which satisfies the conditional formatting

[Anonymous]

I have the following data:

I want to colour XA green, if XA>YA and red, if not.

Similarly colour XB green if XB>YB and red if not.

Is this possible in PBI, will i need to create a measure?

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Well, you can create measures like

Measure = 
var _XA=CALCULATE(SUM('Table'[A]),FILTER(ALL('Table'),[Region]="X"))
var _YA=CALCULATE(SUM('Table'[A]),FILTER(ALL('Table'),[Region]="Y"))
RETURN IF(_XA>_YA&&MAX('Table'[Region])="X","green",IF(_XA<=_YA&&MAX('Table'[Region])="X","red"))

Measure 2 = 
var _XB=CALCULATE(SUM('Table'[B]),FILTER(ALL('Table'),[Region]="X"))
var _YB=CALCULATE(SUM('Table'[B]),FILTER(ALL('Table'),[Region]="Y"))
RETURN IF(_XB>_YB&&MAX('Table'[Region])="X","green",IF(_XB<=_YB&&MAX('Table'[Region])="X","red"))

Set up the conditional formatting as follows.

Apply conditional table formatting in Power BI - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

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

[Anonymous]

this was extremely helpful, thank you! it works fine.

I also wanted to check incase we have more countries how can this be done? the ouput should be similar

I want to colour XA green, if XA>YA and red, if not.

Similarly colour XB green if XB>YB and red if not.

Attaching a table for such a situation:

Region	A	B	Country
X	42.00%	0.60%	France
Y	42.20%	2.20%	France
X	42.00%	10.80%	UK
Y	43.00%	23.00%	UK

[lbendlin]

Yes, you need measures, and you need an index column so Power Query and Power BI can understand what you mean when you say "compare rows". 

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.