Apply conditional formatting on a matrix based on other value in the matrix

[Theo1403]

Hi all,

I created a matrix that shows the accounts receivable and accounts payable balances for several companies in a group. The values come from a transaction table that consists of all the accounts payable and accounts receivable transactions for all of the companies. The row and column values in the matrix come from different columns in that same transaction table. I want to apply conditional formatting to the matching (green) and not matching (red) values. Therefor I need a measure that sums the balance in the current cell and the balance in related cell (for example: company 2 has an A/P balance on company 1, which matches the A/R balance of company 2 on company 1). The end result should look like the picture attached. This shoud be possible, but I am completely stuck...

[Anonymous · Accepted solution]

Hi @Theo1403 

Here I create a set of sample for your reference:

Write a measure and drag it to a matrix:

_Sum = SUM('Table'[Value])

Then add a measure:

Color =
VAR _currentCompany1 =
    MAX ( 'Table'[Company1] )
VAR _currentCompany2 =
    MAX ( 'Table'[Company2] )
RETURN
    IF (
        [_Sum]
            + CALCULATE (
                [_Sum],
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Company1] = _currentCompany2
                        && 'Table'[Company2] = _currentCompany1
                )
            ) <> 0,
        "Red",
        "Green"
    )

Click the [_Sum] and select the Background color in the Conditional formatting:

Then select the Field value in Format style box and select the [color] in the what field should we base this on?

The result is as follow:

Best Regards

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