It's a matrix totalling problem!

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!