RAG Status Applied to Matrix Column and Card Value

Hi dids86 ,

The issue you're facing is because Power BI doesn't know how to aggregate text values like "R", "A", or "G" at the group level when the matrix is collapsed, so it simply returns the first value it finds, which leads to incorrect RAG status at the Cost Centre level and in the card visual. To fix this, you need to convert the RAG status into numeric scores and use a measure to calculate the worst-case value based on severity. You can assign scores such as 3 for "R", 2 for "A", and 1 for "G", then use MAXX to determine the worst score in the current filter context and return the corresponding status.

Start with this calculated column to assign scores:

RAG Score = 
SWITCH([RAG],
    "R", 3,
    "A", 2,
    "G", 1
)

Then create a measure to calculate the worst RAG in the current context:

Worst RAG Status = 
VAR MaxScore = MAXX(VALUES('YourTable'[Account]), [RAG Score])
RETURN
    SWITCH(MaxScore,
        3, "R",
        2, "A",
        1, "G"
    )

Replace 'YourTable' with your actual table name. Use Worst RAG Status in your matrix and card visuals. This will ensure that when the matrix is collapsed, it reflects the most severe RAG among all child rows, and the card will also reflect the overall worst status rather than defaulting to the first one.

Best regards,