Forum Discussion

dids86's avatar
dids86
Regular Visitor
1 year ago
Solved

RAG Status Applied to Matrix Column and Card Value

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 w...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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's avatar
v-dineshya
Community Support
1 year ago

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's avatar
dids86
Regular Visitor
1 year ago

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's avatar
    v-dineshya
    Community Support
    1 year ago

    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's avatar
      v-dineshya
      Community Support
      1 year ago

      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's avatar
        dids86
        Regular Visitor
        1 year ago

        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