Forum Discussion

Theo1403's avatar
Theo1403
Advocate I
2 years ago
Solved

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

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.