Forum Discussion
Reverse cumulative by group
- 8 years ago
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"
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
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"And thanks for the video link. I will definitely have a look at it asap. Thanks!