custom aggregation in dax?

[ovonel]

I have a Project dimension with columns:

• Level 1:

  • Level 2:

    • Level 3

The aggregarion works fine... Level3, we just display the value, Level2 displays the value only if it is negative... and Level1 is just the sum of Level2

Now, I wanted to add a higher level in the matrix but from Client dimension, I have tried SUMX and it doesn't seem to work. is there a way to achieve it?

[v-xiaosun-msft · Accepted solution]

Hi @ovonel ,

According to your description, I wonder whether I understand your requirement correctly, and here is my solution.

Sample data:

Create a calculated column to make Level2 display the value only if it is negative.

Column =
VAR _level2 =
    CALCULATE (
        SUM ( 'Tabelle1'[value] ),
        ALLEXCEPT ( Tabelle1, Tabelle1[level2] )
    )
RETURN
    IF ( _level2 > 0, 0, _level2 )

Create a measure to return the final output.

Measure =
SWITCH (
    TRUE (),
    ISINSCOPE ( Tabelle1[level3] ), SUM ( Tabelle1[value] ),
    SUM ( Tabelle1[Column] )
)

Output:

Then put a higher level in the matrix, you will get the result below.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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