RAG Status Applied to Matrix Column and Card Value

[dids86]

Hi, 

I'm trying to apply a RAG Status as a column in a matrix and to the callout value in a card.

In my example I'd want the correct status to show against the Cost Centres so 10000 would be R when not expanded, 20000 would be G, 30000 would be A as per below. Then when expanded the correct RAG status per row would show.

However in my matrix it is only taking the first status for the overall Cost Centre totals

And also to the card value it just applies A.

Is there any way to achieve my desired outcome do you know?

Thanks

[v-dineshya]

Hi @dids86 ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

I took sample data based on your screenshots.

Please follow below steps.

1. created calculated column based on below code.

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

2. Created measure based on below DAX .

RAG Rollup1 =

VAR RCount = CALCULATE(COUNTROWS('Table'), 'Table'[RAG] = "R")

VAR ACount = CALCULATE(COUNTROWS('Table'), 'Table'[RAG] = "A")

VAR GCount = CALCULATE(COUNTROWS('Table'), 'Table'[RAG] = "G")

VAR Total = RCount + ACount + GCount

RETURN

    SWITCH(TRUE(),

        RCount = 0 && ACount >= 0 && GCount >= 0, "G",            

        ACount > 0 && GCount = 0, "A",                            

        ACount > 0 && RCount > 0 && GCount = 0, "A",               

        RCount > 0, "R",                                           

        BLANK()

    )

3.  Drag the required fileds and measures and calculated column into matrix visual.

Please find the attached PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[dids86]

Thanks @v-dineshya 

No that hasnt worked unfortunately

Below is what I'd expect the Matrix to show

But instead the powerbi shows as 

Without the Account I'd expect is to show as: 

But instead it shows as:

Thanks for your help with this

[v-dineshya]

Hi @dids86 ,

Please refer the output snap.

RAG Rollup7 =

VAR RCount = CALCULATE(COUNTROWS('Table'), 'Table'[RAG] = "R")

VAR ACount = CALCULATE(COUNTROWS('Table'), 'Table'[RAG] = "A")

VAR GCount = CALCULATE(COUNTROWS('Table'), 'Table'[RAG] = "G")

VAR Total = RCount + ACount + GCount

RETURN

    SWITCH(TRUE(),

        RCount = 0 && ACount >= 0 && GCount >= 0, "G",             -- all rows are A or G

        ACount > 0 && GCount = 0, "A",                             -- A only

        ACount > 0 && RCount > 0 && GCount = 0, "A",               -- A and R, no G

        RCount > 0, "R",                                           -- fallback to R

        BLANK()

    )
As per the logic, i have tried all the possible options, but we can't match the exact value you are expecting. It is not possible to match the exact result, which you are expecting.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @dids86 ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[dids86]

Hi @v-dineshya 

If I disregard the Account detail and just want to return the RAG status against the Cost Centre totals instead is there a way to do this do you know?

So essentially I'm just looking to bring the R against 10000, G against 20000 and A against 30000 

Thanks

[v-dineshya]

Hi @dids86 ,

I am trying to resolve your issue, but i tried all the possible approaches . Will give you update as soon as possible. 

Thanks

[v-dineshya · Accepted solution]

Hi @dids86 ,

Please refer the below output snap and attached PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @dids86  ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[DataNinja777]

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,

[dids86]

Thanks @DataNinja777 

I managed to create the measure as below

To note, the only way it would allow me to inlcude the [RAG Score] in the formula was if Table Data without [Account] being included. If [Account] is included I get the following error message

Using the formula that worked, it's not quite giving me the right result as for overall result against Cost Centre 20000 I'd expect a result of G rather than A and for Cost Centre 30000 I'd expect a result of A rather than R as shown in my original post. Below is what powerbi returns. 

I've included the background table data for reference 

Thanks