Reverse cumulative by group

[Hoho1]

Hello,

I'm very new to PowerBI and apologies if something similar has already been asked.

I have the following table where I have scores by month (grouping variable) with a count for each score. I need to get a reverse cumulative column that will sum all values in the Count column that belong to Index values greater than each row's index value for each of the months. And then I want to divide that by the month total count.  Easy! In Excel it looks like this:

 I don't neccessarily need to do everything in one step but I would prefer a solution in M rather than DAX. Obviously I've got more thant 2 months of data. I've gotten to the point in M that I get my Index column but can't figure out how to get a reverse cumulative by group.

Thank you!

[MarcelBeug · Accepted solution]

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"

[Hoho1]

After checking out your video, everything sort of makes sense! Your solution worked like a charm!

Thank you very much for your help MarcelBeug!  

[Hoho1]

And thanks for the video link. I will definitely have a look at it asap. Thanks!

[Hoho1]

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!