Search code examples
excelpowerbipowerquerym

Calculate cumulative values for each row based on another index column


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

Solution

  • enter image description here

    enter image description here

    let a = Table.SelectRows(#"Changed Type", (x)=> 
    x[Index] >= [Index]), 
    b = List.Sum(a[Value]) 
    
    in b