Forum Discussion
Summing with filter changing each row
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?
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
3 Replies
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.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 Then I misunderstood, I thought you wanted a measure, not a calculated column
