Forum Discussion
Reverse cumulative by group
You can first perform the calculations without grouping, then turn that query into a function,
Then use that function in combination with Table.Group.
This 8 minute video illustrates the steps in a similar case, including some specifics I also used in this case (buffering, using Value.Type).
By the way, there is some discrepancy between your text and your formula, as Counts are summed with an Index greater than the current Index, not with a Score greater than the current Score.
Function Calculate:
(Table as table) as table => let Source = Table, Counts = List.Buffer(Source[Count]), AddedCalc = Table.AddColumn(Source, "Calc", each List.Sum(List.Skip(Counts,[Index])&{0}) / List.Sum(Counts), Percentage.Type) in AddedCalcQuery Output (Table1 is your data, already available as a query in Power Query):
let Source = Table.Buffer(Table1), #"Grouped Rows" = Table.Group(Source, {"Month"}, {{"AlRows", Calculate, Value.Type(Calculate(Source))}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Month"}), #"Expanded AlRows" = Table.ExpandTableColumn(#"Removed Columns", "AlRows", {"Month", "Score", "Count", "Index", "Calc"}) in #"Expanded AlRows"
Community ChampionYou can first perform the calculations without grouping, then turn that query into a function,
Then use that function in combination with Table.Group.
This 8 minute video illustrates the steps in a similar case, including some specifics I also used in this case (buffering, using Value.Type).
By the way, there is some discrepancy between your text and your formula, as Counts are summed with an Index greater than the current Index, not with a Score greater than the current Score.
Function Calculate:
(Table as table) as table =>
let
Source = Table,
Counts = List.Buffer(Source[Count]),
AddedCalc = Table.AddColumn(Source, "Calc", each List.Sum(List.Skip(Counts,[Index])&{0}) / List.Sum(Counts), Percentage.Type)
in
AddedCalc
Query Output (Table1 is your data, already available as a query in Power Query):
let
Source = Table.Buffer(Table1),
#"Grouped Rows" = Table.Group(Source, {"Month"}, {{"AlRows", Calculate, Value.Type(Calculate(Source))}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Month"}),
#"Expanded AlRows" = Table.ExpandTableColumn(#"Removed Columns", "AlRows", {"Month", "Score", "Count", "Index", "Calc"})
in
#"Expanded AlRows"After checking out your video, everything sort of makes sense! Your solution worked like a charm!
Thank you very much for your help MarcelBeug! :smileyhappy: :robothappy:
Thanks for your reply, MarcelBeug.
You are indeed right, I should have written "Index greater than the current Index". I'm sorry about that.
I must be doing this wrong, but please bear with me as I'm a new user.
Not knowing how to create a new function, I pasted the 1st bit of code you wrote in my existing code for that table but that didn't give me exactly what I needed as the Calc isn't decreasing to 0% for the last value of the Index column for each of the months. So I think it's looking overall at all months and then providing the cumulative sum?
When I tried the 2nd bit of code, PowerBI gave me an error message that the "import Calculate matches no exports". What is "Calculate" in this line?
#"Grouped Rows" = Table.Group(Source, {"Month"}, {{"AlRows", Calculate, Value.Type(Calculate(Source))}}),And why do you add a zero to the Index here:
AddedCalc = Table.AddColumn(Source, "Calc", each List.Sum(List.Skip(Counts,[Index])&{0}) / List.Sum(Counts), Percentage.Type)It would be great help if you could explain what is the List.Buffer, List.Sum and List.Skip.
Thanks again for your help!
And thanks for the video link. I will definitely have a look at it asap. Thanks!
