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 Exc...
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
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