Create report with running totals

[lottieritchie]

I need to create a report with running totals where certain values exist in the source data table.

The source table has a column "city" and there are multiple values in this city column. There is another column which lists a value for "week purchased".   I need my report to only calculate the running count total for "week purchased" where "city" equals say Manchester. I want it to ignore all the other values where the city is something different. Help please!

[Anonymous · Accepted solution]

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):

City	Week Purchased
MCH	10
LDN	5
MSK	12
PAR	8
MCH	5
LDN	4
MSK	3

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