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!

Forum Discussion

Totals in Matrix

Matrix Visual Hierarchy Level Problem

Yet Another Matrix Total Problem

Matrix Totals Incorrect

Formatting grand total in matrix