cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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

 A B C Opening Stock 59 12508.92 201925 59 -2843.1 201926 12508.91667 59 -3784.75 201927 9665.81667 59 -1225.71 201928 5881.06667 59 4297.863 201929 4655.35667 59 2311.908 201930 8953.219803 59 -1143.34 201931 11265.12787 59 2283.867 201932 10121.78334 59 -425.983 201933 12405.65 59 -1411.55 201934 11979.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

Accepted Solutions
Established Member

## Re: Sum If

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"```
7 REPLIES 7
Super User IV

## Re: Sum If

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])))`

Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: Sum If

Am I able to use that in Power Query?

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

Established Member

## Re: Sum If

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

Frequent Visitor

## Re: Sum If

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

Established Member

## Re: Sum If

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

Established Member

## Re: Sum If

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"```
Frequent Visitor

## Re: Sum If

This looks perfect. Thanks so much!!

## Helpful resources

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors