Given a table:
Index | Value |
---|---|
1 | 2.6 |
2 | 7.6 |
3 | 5.6 |
4 | 6.1 |
I would like to calculate a cumulative sum for each row, where the sum is calculated by summing the Value column where indices are equal or larger than the Index value for current row, so that:
Index | Value | Value_Cum |
---|---|---|
1 | 2.6 | 21.9 |
2 | 7.6 | 19.3 |
3 | 5.6 | 11.7 |
4 | 6.1 | 6.1 |
using Power Query M language in Excel.
To illustrate the logic better, the Value_Cum column was calculated, using Excel Table syntax, with the following formula:
=SUM(FILTER([Value];[Index]>=[@Index]))
Edit:
In response to @horseyride proposed solution, it has to also work for cases where there are multiple identical values of Index as well as when the Index is not sorted.
Additonal example, where the source table with the cumulative value is as follows:
Index | Value | Value_Cum |
---|---|---|
7 | 2.3 | 26.1 |
2 | 9.9 | 56.7 |
6 | 3 | 29.1 |
3 | 2.5 | 46.8 |
10 | 9.2 | 23.8 |
10 | 5.9 | 23.8 |
3 | 8 | 46.8 |
4 | 7.2 | 36.3 |
10 | 8.7 | 23.8 |
and if I create a Custom column with power query using the formula:
= Table.AddColumn(#"Sorted Rows", "Custom", each List.Sum(List.RemoveFirstN(Source[Value],[Value]-1)))
it results in errors, see the results (empty values are errors):
Index | Value | Value_Cum | Custom |
---|---|---|---|
2 | 9.9 | 56.7 | |
3 | 8 | 46.8 | 15.9 |
3 | 2.5 | 46.8 | |
4 | 7.2 | 36.3 | |
6 | 3 | 29.1 | 44.5 |
7 | 2.3 | 26.1 | |
10 | 8.7 | 23.8 | |
10 | 9.2 | 23.8 | |
10 | 5.9 | 23.8 |