Running sum invert

[Sarutra]

Hi,

The function correctly calculates the cumulative sum. How to modify it so that the last row becomes 1, the second-to-last becomes 2, and so on, with the first row being the final cumulative sum? 

( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
Source = MyTable,
BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
RunningTotal =
List.Generate (
() => [ RT = BuffValues{0}, RowIndex = 0 ],
each [RowIndex] < List.Count(BuffValues),
each [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
RowIndex = [RowIndex] + 1 ],
each [RT] ),
#"Combined Table + RT" =
Table.FromColumns(
Table.ToColumns( MyTable )
& { Value.ReplaceType( RunningTotal, type {Int64.Type} ) } ,
Table.ColumnNames( MyTable ) & { RTColumnName } )
in
#"Combined Table + RT"

Thank you for the answers

Arturas

[Sarutra]

Hi,

I get an incorrect result.
The result must be

Row value rezult

A        10     40

B         20    30

C         10    10

[tharunkumarRTK]

@Sarutra 

I tried writing in a different way, I tested it and its working. can you try ?

( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
    Source = MyTable,
    BuffValues = List.Buffer(Table.Column(MyTable, ValueColumn)), 
    TotalRows = List.Count(BuffValues), 
    RunningTotal =
        List.Generate(
            () => [RT = List.Sum(BuffValues), RowIndex = TotalRows - 1], 
            each [RowIndex] >= 0, 
            each [ 
                RT = [RT] -  BuffValues{[RowIndex]}, 
                RowIndex = [RowIndex] - 1 
            ],
            each [RT] 
        ),
    #"Combined Table + RT" =
        Table.FromColumns(
            Table.ToColumns(MyTable) & {Value.ReplaceType(RunningTotal, type {Int64.Type})},
            Table.ColumnNames(MyTable) & {RTColumnName}
        )
in
    #"Combined Table + RT"

 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Sarutra]

work not corect

row   value  rezult   tru rezult

A        10      70        70

B         15     45         60

C         20     25        45

D         25   10         25

[tharunkumarRTK · Accepted solution]

@Sarutra 

Added both versions here:

( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
    Source = MyTable,
    BuffValues = List.Buffer(Table.Column(MyTable, ValueColumn)), 
    TotalRows = List.Count(BuffValues), 
    RunningTotal =
        List.Generate(
            () => [RT = List.Sum(BuffValues),RT1= List.Sum(BuffValues),RowIndex = TotalRows,RowIndex0 = 0 ], 
            each [RowIndex] > 0, 
            each [ 
                RT = [RT] -  BuffValues{[RowIndex]-1}, 
                RT1= [RT1] - (BuffValues{[RowIndex0] }),
                RowIndex = [RowIndex] - 1 ,
                RowIndex0 = [RowIndex0] + 1
            ],
            each [[RT],[RT1]]
        ),
    #"Combined Table + RT" =
        Table.FromColumns(
            Table.ToColumns(MyTable) & {RunningTotal},
            Table.ColumnNames(MyTable) & {RTColumnName}
        )
in
    #"Combined Table + RT"

Also I am attavhing the PBIX file

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Sarutra]

Super, it works

thanks

[tharunkumarRTK]

@Sarutra 

( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
    Source = MyTable,
    BuffValues = List.Buffer(Table.Column(MyTable, ValueColumn)), 
    TotalRows = List.Count(BuffValues), 
    RunningTotal =
        List.Generate(
            () => [RT = BuffValues{TotalRows -1}, RowIndex = TotalRows - 1], 
            each [RowIndex] >= 0, 
            each [ 
                RT = [RT] + BuffValues{[RowIndex]}, 
                RowIndex = [RowIndex] - 1 
            ],
            each [RT] 
        ),
    ReversedRT = List.Reverse(RunningTotal), 
    #"Combined Table + RT" =
        Table.FromColumns(
            Table.ToColumns(MyTable) & {Value.ReplaceType(ReversedRT, type {Int64.Type})},
            Table.ColumnNames(MyTable) & {RTColumnName}
        )
in
    #"Combined Table + RT"

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!