It's a matrix totalling problem!

[HM615]

Hello, 

There are many, many posts about the issues with getting matrix totals and subtotals to behave in the way you want, but I am too inexperienced a coder to be able to extrapolate from the examples I can find to the problem that I have! My matrix measure is particularly complicated becuase: a) it is made up of several other measures, b) there is a triple layer split in my data - I have a row for each Subject, Subjects are grouped into Schools, Schools are then grouped into Colleges, and I need a final grand total, and c) I have some hard coding for particular Subjects, because for these I want to ignore the calculation being performed for most Subjects and just use a hard coded figure. With me so far? 

My measure looks like this: 

Estimated Pre-Clearing Reg Home =

VAR College         = SELECTEDVALUE(srs_mcr[College])

VAR School          = SELECTEDVALUE(srs_mcr[School])

VAR Department      = SELECTEDVALUE(srs_mcr[Subject])

VAR MainResultRaw =

        factCap2026[UF Estimate Home]

        + factCap2026[CF Estimate Home]

        + factCap2026[Insurance Estimate Home]

        + factCap2026[NoResponse Estimate Home]

        + factCap2026[Awaiting Estimate Home]

        + factFutureReg[UGY01_futurereg HOME]

        + factDrl2025ChangeCourse[Home Change Course who reg]

        + [FY Progression Home]

VAR MainResultAdjusted =

    IF(Department = "Medicine"                       , 272,

    IF(Department = "Medicine Foundation"             , 35,

    if(Department = "Pharmacy"                             , 60,

    if(Department = "Physiotherapy"                        , 75,

    if(Department = "Physiotherapy Foundation"        , 60,

    if(Department = "Nursing"                              , 85,

    if(Department = "Midwifery"                            , 50,

    if(Department = "Radiography"                          , 32,

    IF(Department = "ODP"                                  , MainResultRaw + 27,

    IF(School = "Medical School"    && isblank(Department) , 307,      //school total

    IF(School = "Healthcare" && ISBLANK(Department), MainResultRaw + 389,               //school total

    IF(College = "CLS" && ISBLANK(School) && ISBLANK(Department), MainResultRaw + 696,   //college total

    IF(isblank(College)    && ISBLANK(School)       && isblank(Department), MainResultRaw + 696,      //grand total

    //ELSE

    MainResultRaw

    )    )    )   ) ) ))))))))

Return

    //MainResultRaw

   MainResultAdjusted

It is working correctly at the row level (i.e. the figure for individual Subjects looks as I expect). Snip included. The School level subtotal is correct for everyone EXCEPT the School for which I have the most hard coding. The Medical School subtotal looks fine, but the Healthcare one looks wrong. I'm not sure why. The College subtotals all look wrong - none of them reflect the sum of the Schools totals. I think I need to do something with SUMX, but I can't work out how to incorporate it into the complex measure that I have. I also have no idea why the Healthcare subtotal doesn't look correct, although the Medical School one does. I'm also getting a blank row, with the hard-coded total, which I can fix by excluding blank subjects, but I'm not entirely sure how/why that's happening either! In case not already obvious, I am a very inexpert coder, and have come to Power BI from the systems side, so this is stretching the limits of my understanding!!

Does anyone have any advice?

Thank you! 

[v-sshirivolu]

Hi @HM615 ,

I would take a moment to thank @djurecic  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[HM615 · Accepted solution]

It wasn't an ISINSCOPE issue. I had to amend the way it was summing things in each of the individual measures. Once I had done that, the original coding for the main measure worked correctly. 

[djurecic]

Hi @HM615 ,

 I would look at using ISINSCOPE to determine what level of the heirarchy you are at:

https://learn.microsoft.com/en-us/dax/isinscope-function-dax

https://dax.guide/isinscope/

[HM615]

Thank you for this suggestion. I am still trying to work out how I incorporate this into my particular measure. Given that the elements that make up my MainResultRaw (below) are all individual measures, and not columns, would this still be the way to go? 

VAR MainResultRaw =

        factCap2026[UF Estimate Home]

        + factCap2026[CF Estimate Home]

        + factCap2026[Insurance Estimate Home]

        + factCap2026[NoResponse Estimate Home]

        + factCap2026[Awaiting Estimate Home]

        + factFutureReg[UGY01_futurereg HOME]

        + factDrl2025ChangeCourse[Home Change Course who reg]

        + [FY Progression Home]