Summing with filter changing each row

[Anonymous]

My data does not have a correct value for the highest level in the hierachy. The value should be calculated based on all the values belonging to the same main project.

I can calculate this in Excel quite easily with the following formula

=IF(A2=B2; SUMIF($A$2:$A$17;"="&B2;$C$2:$C$17); C2)

to get the desired output in the table below.

Main project	Project number	Value	CALC
A	A	0	-237700
A	A1	-134100	-134100
A	A2	-103600	-103600
B	B	15000	-1864449,97
B	B1	-338250	-338250
B	B2	-451699,98	-451699,98
B	B3	-418999,74	-418999,74
B	B4	-670500,25	-670500,25
C	C	0	-2013599,94
C	C1	-338250	-338250
C	C2	-451699,98	-451699,98
C	C3	-418999,98	-418999,98
C	C4	-804649,98	-804649,98
D	D	0	-789949,98
D	D1	-338250	-338250
D	D2	-451699,98	-451699,98

I cannot figure out how to do this same calculation with Power BI. Either with the query or DAX is fine.

I tried using the row by row SUMX with FILTER but I am not getting a correct output.

CALC = SUMX(FILTER('Table'; 'Table'[Main project] == 'Table'[Project number]); 'Table'[Value])

Main project	Project number	Value	CALC
A	A	0	15000
A	A1	-134100	15000
A	A2	-103600	15000
B	B	15000	15000
B	B1	-338250	15000
B	B2	-451699,98	15000
B	B3	-418999,74	15000
B	B4	-670500,25	15000
C	C	0	15000
C	C1	-338250	15000
C	C2	-451699,98	15000
C	C3	-418999,98	15000
C	C4	-804649,98	15000
D	D	0	15000
D	D1	-338250	15000
D	D2	-451699,98	15000

How do I achieve this in Power BI?

[sturlaws]

Hi @Anonymous,

you can try this code:

Measure =
SUMX (
    'Table';
    IF (
        'Table'[Main project] = 'Table'[Project number];
        CALCULATE (
            SUM ( 'Table'[Value] );
            FILTER (
                ALL ( 'Table' );
                'Table'[Main project] = MIN ( 'Table'[Main project] )
            )
        );
        SUM ( 'Table'[Value] )
    )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous · Accepted solution]

That did not work correctly. It gives the same value for each row. But luckily I managed to figure out a way to do it. Below table contains both calculations. Correct one in CALC and your suggestion in Measure.

CALC = SUMX(
    FILTER(
        'Table'; 
        'Table'[Main project] == EARLIER('Table'[Project number]) || 
        'Table'[Project number] == EARLIER('Table'[Project number])
        ); 
    'Table'[Value]
)

Main project	Project number	Value	CALC	Measure
A	A	0	-237700	-59819198,68
A	A1	-134100	-134100	-59819198,68
A	A2	-103600	-103600	-59819198,68
B	B	15000	-1864449,97	-59819198,68
B	B1	-338250	-338250	-59819198,68
B	B2	-451699,98	-451699,98	-59819198,68
B	B3	-418999,74	-418999,74	-59819198,68
B	B4	-670500,25	-670500,25	-59819198,68
C	C	0	-2013599,94	-59819198,68
C	C1	-338250	-338250	-59819198,68
C	C2	-451699,98	-451699,98	-59819198,68
C	C3	-418999,98	-418999,98	-59819198,68
C	C4	-804649,98	-804649,98	-59819198,68
D	D	0	-789949,98	-59819198,68
D	D1	-338250	-338250	-59819198,68
D	D2	-451699,98	-451699,98	-59819198,68

[sturlaws]

Then I misunderstood, I thought you wanted a measure, not a calculated column