Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |