Making grand total do the same thing as individual rows

[EpicTriffid]

Morning all,

I have built a report where I have several years of data. A year in the middle of that data is the current year (2022/23), and I have three columns which work out the difference between certain years and the current year. But in order to do that, i first need the "Diff" columns to pull in the data for 2022/23 first. You can see my progress below (Ignore the gaps in the conditional formatting!):

The code that I use to calculate the current year data is:

VAR currentYeartarget = 
    CALCULATE(
    SUM('Base Data'[Total Student Count]), 
    ALLEXCEPT(
        'Base Data',
        'Base Data'[Department],
        'Base Data'[Faculty],
        'Base Data'[Mode and Code], 
        'Base Data'[Course Title], 
        'Base Data'[Fee Status],
        'Base Data'[Mode 4-way],
        'Base Data'[Study Level Detailed], 
        'Base Data'[Study Level 4-way],
        'Base Data'[Forecast/Actuals]),
    'Base Data'[Course Year of Study] = 1,
    AND('Base Data'[Forecast/Actuals] <> "Differences", 'Base Data'[Forecast/Actuals] <> "Targets"),
    'Base Data'[Numbers Type] = "Targets",
    'Base Data'[Year Index] = 3)

This allows me to both get the current year of data from across the matrix, but then also allows me to use the slicers above and still have the correct value.

So, in the image, for the individual rows you can see that it pulls across the correct data. But when we get to the subtotals and grand total, it aggregates rather than pulling across the value from the 2022/23 column. I feel like this might be a simple solution in using HASONEFILTER, but I don't know how to apply it, how which column to apply it to? Would I need to have a different HASONEFILTER for each level of the drilldown?

This is really boggling my brain, and any help would be greatly appreciated!

[EpicTriffid · Accepted solution]

This is fixed now. I had omitted certain columns from the ALLEXCEPT.