Forum Discussion
Running sum invert
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
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!
6 Replies
( 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!
Hi,
I get an incorrect result.
The result must beRow value rezult
A 10 40
B 20 30
C 10 10
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!
work not corect
row value rezult tru rezult
A 10 70 70
B 15 45 60
C 20 25 45
D 25 10 25
