Conditional Formatting for Matrix Based on Movement

[mojekk]

I have a sample data as below:

ACCOUNT ID	PREVIOUS RATING	CURRENT RATING	OUTSTANDING BALANCE	RATING MOVEMENT
TXPOJ	1	1	$ 291.31	REMAIN
YYECT	9	11	$ 540.40	DOWNGRADE
UMTMH	5	12	$ 978.56	DOWNGRADE
UFVLV	4	6	$ 570.59	DOWNGRADE
XACKM	8	4	$ 549.44	UPGRADE
YXLHA	9	11	$ 909.79	DOWNGRADE
JECLT	4	5	$ 845.84	DOWNGRADE
LOFBN	1	3	$ 68.00	DOWNGRADE
VKQDO	2	5	$ 140.86	DOWNGRADE
GODYZ	6	7	$ 363.34	DOWNGRADE
YRCHL	3	11	$ 667.65	DOWNGRADE
NVQOZ	12	1	$ 165.71	UPGRADE
UXHMB	2	7	$ 123.50	DOWNGRADE
UXYJZ	6	6	$ 881.34	REMAIN
OBTFU	3	2	$ 17.52	UPGRADE
MKSYX	11	1	$ 815.95	UPGRADE
OVYYH	9	9	$ 254.64	REMAIN
ZLGVP	3	6	$ 641.07	DOWNGRADE
BEWNR	5	10	$ 935.71	DOWNGRADE
UOXJT	5	5	$ 167.13	REMAIN
IXNUP	11	6	$ 102.83	UPGRADE
CPFTS	8	7	$ 631.63	UPGRADE
ZDMGZ	10	2	$ 339.78	UPGRADE
BJBNZ	9	11	$ 291.88	DOWNGRADE
CEUOW	9	5	$ 222.41	UPGRADE
QBMBK	2	8	$ 764.01	DOWNGRADE
FQHWC	2	4	$ 789.33	DOWNGRADE
HKUGC	6	10	$ 279.36	DOWNGRADE
LVCTG	8	1	$ 941.53	UPGRADE
BIRCZ	7	8	$ 884.07	DOWNGRADE

And I have created a matrix in Power BI and my end goal is to have it coloured based on its Rating Movement. Below is the matrix that I have created in Excel:

The Red is Downgrade, Green Upgrade and Grey is Remain. I have created DAX query to be used in conditional formatting like SWITCH (TRUE()) and create a table so that blank cell filled with zero. But nothing works. Appreciate if any expert can assist me on resolving this issue. 

[Anonymous · Accepted solution]

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.

[speedramps]

Download this solution from Onedrive

Click here 

RAG = 
var mymovemnet = SELECTEDVALUE(Yourdata[RATING MOVEMENT])
 RETURN
 SWITCH(SELECTEDVALUE(Yourdata[RATING MOVEMENT]),
 	"DOWNGRADE","Red",
    "UPGRADE","Green",
    "REMAIN","Grey"
    )

Please click the [accept as solution] and the thumbs up button. Thank you

[mojekk]

Appreciate the help, but does not appreciate the tone. Thanks again, but that simple solution has been done previously by me which only coloured cell containing the data and I need even the empty cell are coloured accordingly (as in Excel). Wanted to accept it as solution, but still not answering my initial request.