Isolating values from specific column in matrix

[EpicTriffid]

Hi all,

Bit of a difficult one to explain this, but in the screenshot below, I am attempting to calculate the difference between forecasted student numbers in the future, and the actual financial year we are currently in (ignore the fact that it says 21/22 instead of 22/23).

So at the moment I am attempting to isolate the values in the 2021/22 column and bring them into the 1 Year Diff %, 3 Year Diff % and 5 Year Diff % columns. I have the differences, that's fine. All I need it to make those Diff columns show the values in 2021/22 so it would look like this (1 row as example):

	2019/20	2020/21	2021/22	2022/23	2023/24	2024/25	2025/26	2027/28	1 year diff	3 year diff	5 year diff
UGFE	196	149	163	172	175	178	180	182	163	163	163

The values in 2021/22 could be changed by any of the slicers at the top of the page, which would then be reflected in the values in the 1, 3 and 5 year diff columns.

I have tried ALLEXCEPT as below, but when i take into account all the columns that could be filtered, it blanks out everything except the totals, as you can see in the screenshot below:

CALCULATE(
    SUM('Base Data'[Total Student Count]), 
    ALLEXCEPT(
        'Base Data', 
        'Base Data'[Department],
        'Base Data'[Faculty],
        'Base Data'[Mode and Code],
        'Base Data'[Course Mode],
        'Base Data'[Fee Status],
        'Base Data'[Course Title],
        'Base Data'[Forecast/Actuals],
        'Base Data'[Course Level (Grp 2)], 
        'Base Data'[Course Level (Grp 1)], 
        'Base Data'[Numbers Type]),
    'Base Data'[Course Year of Study] = 1, 
    'Base Data'[Year Index] = 3)

I feel like I'm really close on this, but that I'm just not understanding ALLEXCEPT enough to make it work.

Any help would be greatly appreciated!

[Anonymous]

Hi @EpicTriffid 

Did you create the dax in measure, you can try to put the dax in a measure, then put the measure to the matrix

measure=
CALCULATE(
    SUM('Base Data'[Total Student Count]), 
    ALLEXCEPT(
        'Base Data', 
        'Base Data'[Department],
        'Base Data'[Faculty],
        'Base Data'[Mode and Code],
        'Base Data'[Course Mode],
        'Base Data'[Fee Status],
        'Base Data'[Course Title],
        'Base Data'[Forecast/Actuals],
        'Base Data'[Course Level (Grp 2)], 
        'Base Data'[Course Level (Grp 1)], 
        'Base Data'[Numbers Type]),
    'Base Data'[Course Year of Study] = 1, 
    'Base Data'[Year Index] = 3)

If it cannot work, can you provide sample pbix file?

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.