Cumulative SUM (SUMIFS) based on conditions

[JustDavid]

Below is a simple data where I have UID, Cost Code, Period and Net Posting Amount.

As you can see, although my data is sorted (in my actual data, it's UNSORTED), to show case the issue, my "dates" are not continuous.

I would like to have a new column 'Desired Result' in Power Query to do a cumulative sum or SUMIFS based on the columns UID, Cost Code and Period.

The logic to the cumulative sum or SUMIFS is

Beg. Balance + SUM(period 1 : current_row_period)

BASED ON GROUP of columns, which in this case is [UID] and [Cost Code]

For example, in the screenshot of row 6. The cumulative sum/sumifs would be

<Beg. Balance (if any)> + (SUM(<period 1 (if any)> + <period 2 (if any)> + <period 3 (if any)> + <period 4 (if any)> + <period 5 (if any)> + <period 6 (current row)>)

Which is equal to in numeric value

Beg. Balance =   found          = 10
period 1     =   found          = 2
period 2     =   not found      = 0
period 3     =   found          = 3
period 4     =   not found      = 0
period 5     =   found          = -5
period 6     =   found          = 4
________________________________
Cumulative Sum/SUMIFS @ period 6 = 14

Another example, say row 16 of the screnshot

<Beg. Balance (if any)> + (SUM(<period 1 (if any)> + ... + <period 11 (current row)>)

Which is equal to in numeric value

Beg. Balance =   not found      = 0
period 1     =   not found      = 0
period 2     =   not found      = 0
period 3     =   not found      = 0
period 4     =   not found      = 0
period 5     =   not found      = 0
period 6     =   found          = 5
period 7     =   not found      = 0
period 8     =   found          = 3
period 9     =   not found      = 0
period 10    =   found          = -6
period 11    =   found          = 2
________________________________
Cumulative Sum/SUMIFS @ period 11 = 44

Excel File 

[v-stephen-msft · Accepted solution]

Hi @JustDavid ,

According to your desired result based on data, I think you want to add up the amount in groups.

Here're two methods.

Method1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoyNDAEUk6p6QpOiTmJecmpQJ6hgVKsTrQSkAXiALEREt8YjBF8UyDWNUUSMANiEyS+OUgB3ASghUYYFlogKbdEUQ5xDbqIIYaIEdglIAEnhK/M4IIQVRZoToeYbIZuMobBukBNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UID = _t, #"Cost Code" = _t, Period = _t, #"Net Posting Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UID", type text}, {"Cost Code", type text}, {"Period", type text}, {"Net Posting Amount", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"UID", "Cost Code", "Period", "Net Posting Amount"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Cost Code", "UID"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"UID", "Cost Code"}, {{"allrows", each _, type table [UID=nullable text, Cost Code=nullable text, Period=nullable text, Net Posting Amount=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "table", each Table.AddIndexColumn([allrows],"index",1,1)),
    Custom1 = Table.TransformColumns(#"Added Custom", {"table" , (x) => Table.AddColumn(x,"newcol", each List.Sum(List.FirstN(x[Net Posting Amount],[index])))}),
    #"Expanded table" = Table.ExpandTableColumn(Custom1, "table", {"Period", "Net Posting Amount", "newcol"}, {"Period", "Net Posting Amount", "newcol"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded table",{"allrows"})
in
    #"Removed Columns"

Method2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoyNDAEUk6p6QpOiTmJecmpQJ6hgVKsTrQSkAXiALEREt8YjBF8UyDWNUUSMANiEyS+OUgB3ASghUYYFlogKbdEUQ5xDbqIIYaIEdglIAEnhK/M4IIQVRZoToeYbIZuMobBukBNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UID = _t, #"Cost Code" = _t, Period = _t, #"Net Posting Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UID", type text}, {"Cost Code", type text}, {"Period", type text}, {"Net Posting Amount", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"UID", "Cost Code", "Period", "Net Posting Amount"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Cost Code", "UID"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"UID", "Cost Code"}, {{"allrows", each _, type table [UID=nullable text, Cost Code=nullable text, Period=nullable text, Net Posting Amount=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "table", each Table.AddIndexColumn([allrows],"index",1,1)),
    #"Expanded table" = Table.ExpandTableColumn(#"Added Custom", "table", {"Period", "Net Posting Amount", "index"}, {"Period", "Net Posting Amount", "index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded table", "Custom", each let 
_UID =[UID],
_Cost_Code = [Cost Code],
_Index = [index]
in
List.Sum( Table.SelectRows(#"Expanded table", each [UID] = _UID and [Cost Code] = _Cost_Code and [index]<=_Index)[Net Posting Amount])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each let 
_UID =[UID],
_Cost_Code = [Cost Code],
_Index = [index]
in
List.Sum( Table.SelectRows(#"Expanded table", each [UID] = _UID and [Cost Code] = _Cost_Code and [index]<=_Index)[Net Posting Amount])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Custom.1", "Result"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"index", "Custom"})
in
    #"Removed Columns"

You can check more details or see the steps in my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[p45cal]

In the linked-to workbook below is a bit of a guess. It produces what you want but if there is more than one 'Beg Balance' per UID/Cost Code group, or there's some other text in that column it will likely fail. This is because I've taken a guess that you want each group to be sorted numerically by its Period, but I've kept textual entries in that column to the top. As it happens, all your Periods are in order. Maybe it's better not to bother sorting in that case?

The result table is at cell G1.

The query uses a custom function called fnAddCum.

There is another query (Table1 (2)), connection only, which is the same as the results table but incorporates the custom function within it.

It's the 'missing data' bit I haven't twigged at the moment…

The workbook: https://app.box.com/s/3gucl833f1omoflzh09r5ajqlu0fioqd

[lbendlin]

you mentioned that the actual data is unsorted.  Please provide sample data in the original format.

[JustDavid]

I do not have the PQ and that connects directly to company's database.

I've submitted an excel file that mimics the original file and taking only the columsn that I need to do sumifs on.

I'm saying data isn't sorted, but it can be done in the PQ steps to sort it. So after sorting, the data will be like the print screen above