Conditional Formatting for Matrix Based on Movement

Thanks for the reply from speedramps, please allow me to provide another insight.
Hi mojekk ,

 

Please refer to the following steps.

 

Create two rating tables, derived from the PREVIOUS RATING and CURRENT RATING fields, and establish a relationships.Use the fields from these two tables as the rows and columns of the matrix.

Previous rating = GENERATESERIES(MIN('Table'[PREVIOUS RATING]),MAX('Table'[PREVIOUS RATING]),1)

Current rating = GENERATESERIES(MIN('Table'[CURRENT RATING]),MAX('Table'[CURRENT RATING]),1)

 

Creates a measure to display the OUTSTANDING BALANCE and uses this measure as the value field of the matrix.

Sum of Outstanding Balance = 
IF (
    MAX ( 'Table'[RATING MOVEMENT] ) <> BLANK (),
    SUM ( 'Table'[OUTSTANDING BALANCE] ),
    0
)

 
Creates measures for the background color and font color to be used for the matrix values.

BackgroundColor = 
VAR rating_movement = MAX('Table'[RATING MOVEMENT])
VAR previous_rating = MAX('Previous rating'[value])
VAR current_rating = MAX('Current rating'[value])
VAR _result = SWITCH(rating_movement,
        "UPGRADE","green",
        "REMAIN","black",
        "DOWNGRADE","red"
        )
RETURN
SWITCH(TRUE(),
        previous_rating<current_rating, IF(rating_movement<>BLANK(),_result,"red"),
        previous_rating=current_rating,IF(rating_movement<>BLANK(),_result,"black"),
        previous_rating>current_rating,IF(rating_movement<>BLANK(),_result,"green")
)

FontColor = 
IF([Sum of Outstanding Balance]=0,
SWITCH([BackgroundColor],
        "red","red",
        "black","black",
        "green","green"),
        "white"
)

The final resutl is as follows. Hopefully it will meet your needs.

 

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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