Create report with running totals

Hi lottieritchie ,

you need a slightly modified version from here: https://community.powerbi.com/t5/Desktop/How-to-do-a-running-Sum-by-group-in-Power-Query/td-p/290123.

Assuming a table like this (mySource):

This is the PQ code in your main query:

let
    Source = mySource,
    #"Grouped Rows" = Table.Group(Source, {"City"}, {{"AllData", each fnAddRunningSum (_, "Week Purchased", {"MCH"})}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Week Purchased", "Cumul"}, {"Week Purchased", "Cumul"})
in
    #"Expanded AllData"

This is the modified function (fnAddRunningSum):

(MyTable as table, MyColumn as text, myFilter as list) =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "Cumul", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(Table.Column(Source,MyColumn),{0},(seed,add) => seed & {List.Last(seed) + add})),
    Cumul = if List.Contains(myFilter, MyTable[City]{0}) then Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType) else MyTable
in
    Cumul

Please note that you need to pass the "cilty" filter as a list (in {}) even if there is only one city to filter - this version gives you the flexibility to use more than one city.

The result looks like this:

Regards,

JB