Forum Discussion
Solved
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?...
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!
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
|
work not corect
row value rezult tru rezult
A 10 70 70
B 15 45 60
C 20 25 45
D 25 10 25
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!
Super, it works
thanks