Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SiGill1979
Frequent Visitor

Sum If

How can I sum a column if the value is less than a certain value and matches another value.

 

My Data is like this

ABCOpening Stock
5912508.92201925 
59-2843.120192612508.91667
59-3784.752019279665.81667
59-1225.712019285881.06667
594297.8632019294655.35667
592311.9082019308953.219803
59-1143.3420193111265.12787
592283.86720193210121.78334
59-425.98320193312405.65
59-1411.5520193411979.66667

 

I have put this together in Excel but now need to replicate it in Power Query. The Opening Stock Column is what I am trying to achieve. In Excel the formula in Cell D3 down is =SUMIFS(B:B,C:C,"<"&C3,A:A,A3)

How is this written in Power Query

 

Any help would be appreciated.

 

Thanks

 

Simon

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Ooops copied the wrong query AND I did not put it in code block.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
    Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows", 
(tab) => Table.AddColumn(tab, "RunningTotal", each List.Sum(Table.SelectRows(tab, (row) => row[C] < [C])[B]))
, type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
    #"Expanded AllRows"

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

You need to have date/calendar dimension.

sales_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))

Am I able to use that in Power Query?

I have found List.Accumulate but i can't get it to work?

Anonymous
Not applicable

The code below presumes that your data is in a table named "Table2". The process is to add an index column to the table that starts at 0. Then take the First chacters up to the index amount from the prior table B column and sum them up.

 

This technique may be slow if you have a large table. In that case, we would add a List.Buffer as a separate step on the #"Added Index" table.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Opening Stock.1", each List.Sum(List.FirstN(#"Added Index"[B],[Index])))
in
    #"Added Custom"

Regards,

Mike

Thanks Mike

 

That is very close to what I am looking for, thank you, does take a while to run though.

 

But it needs to look at column C and only sum anything less than the current value in column C

And also needs to look at column A and only sum if the current value matches column A. Hope that makes sense.

Hence the =SUMIFS(B:B,C:C,"<"&C3,A:A,A3) is it was in Excel

Anonymous
Not applicable

Hi Simon,

I tried to take a shortcut based on the sample size loaded. I am hoping this code will be more complete and faster

 

Note that because I am grouping by column A, row[A] = [A] is not necessary.

 

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
#"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows",
each Table.AddColumn(_, "RunningTotal", each List.Sum(Table.SelectRows(Source, (row) => row[C] < [C])[B]))
, type table}}),
#"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
#"Expanded AllRows"

 

Regards,

Mike

Anonymous
Not applicable

Ooops copied the wrong query AND I did not put it in code block.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
    Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows", 
(tab) => Table.AddColumn(tab, "RunningTotal", each List.Sum(Table.SelectRows(tab, (row) => row[C] < [C])[B]))
, type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
    #"Expanded AllRows"

This looks perfect. Thanks so much!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors