topic Re: RAG Status Applied to Matrix Column and Card Value in Developer

RAG Status Applied to Matrix Column and Card Value

dids86 — Sat, 24 May 2025 12:10:59 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

DataNinja777 — Sun, 25 May 2025 10:37:52 GMT

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,

Re: RAG Status Applied to Matrix Column and Card Value

dids86 — Sun, 25 May 2025 16:56:17 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

v-dineshya — Mon, 26 May 2025 11:32:48 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

dids86 — Mon, 26 May 2025 12:39:08 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

v-dineshya — Tue, 27 May 2025 13:13:06 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

v-dineshya — Fri, 30 May 2025 16:47:35 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

dids86 — Mon, 02 Jun 2025 14:07:55 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

v-dineshya — Tue, 03 Jun 2025 13:02:31 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

v-dineshya — Wed, 04 Jun 2025 18:48:45 GMT

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

Re: RAG Status Applied to Matrix Column and Card Value

v-dineshya — Mon, 09 Jun 2025 03:56:42 GMT

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.