cancel
Showing results for
Did you mean:
Post Prodigy

## custom aggregation in dax?

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?

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors