Forum Discussion

EpicTriffid's avatar
EpicTriffid
Helper IV
3 years ago

Isolating values from specific column in matrix

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/202020/212021/222022/232023/242024/252025/262027/281 year diff3 year diff5 year diff
UGFE196149163172175178180182163163163

 

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!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.