Power Query SUMIFS Equivalent

[Skip2MiLu]

I am looking for a power query custom column formula that will do the equivalent to this Excel SUMIFS formula below

=SUMIFS(Country_Variety_Sales[2023 TOTAL],Country_Variety_Sales[Secondary No],Country_Variety_Sales[@[Secondary No]],Country_Variety_Sales[Country],Country_Variety_Sales[@Country])

 

[alansidman]

Cannot manipulate data in a picture. Please upload your sample data using XL2BB so that we can test our potential solution without having to retype your whole data source. It is looking like a Group By will be what you need.

 

[Skip2MiLu]

Cannot manipulate data in a picture. Please upload your sample data using XL2BB so that we can test our potential solution without having to retype your whole data source. It is looking like a Group By will be what you need.

Unfortunately, I am not able to install XL2BB on my work computer. I get a "This file type is not supported in Protected View".

Also, I am looking for a power query formula and not and not an excel formula, so I am not sure how much help XL2BB is going to be.

Basically, I looking for a custom column formula within power query that will return the same result as using Excel SUMIFS formula. I also do not want to use power query grouping function to aggregate in this example

Here are my variables using Excel SUMIFS syntax as a reference that I am hoping will help direct you in coming up with the equivalent power query custom column formula. Keep in mind that the table and column names below are the same within power query, I am just not sure of power query syntax in relevant functions I must use:

SUMIFS
Sum_range: Country_Variety_Sales[2023 TOTAL]
Criteria_range 1: Country_Variety_Sales[Secondary No]
Criteria1: Country_Variety_Sales[@[Secondary No]]
Criteria_range 2: Country_Variety_Sales[Country]
Criteria2: Country_Variety_Sales[Country]

 

[alansidman]

Ok. Thanks for the criteria.

= Table.Group(Prior Query Step {"Secondary No", "Country"}, {{"Totals", each List.Sum([2023 TOTAL]), type number}})

this should help to understand this

 

[Skip2MiLu]

Ok. Thanks for the criteria.

= Table.Group(Prior Query Step {"Secondary No", "Country"}, {{"Totals", each List.Sum([2023 TOTAL]), type number}})

this should help to understand this

Hi Alansidman

Excuse my inexperience with Power Query code, but is the formula you provided something that I can paste into a custom column section as per below 1st picture? As this is what I am looking for.

The second picture might help give an overview of what I trying to recreate as a custom column in Power Query instead of doing this in Excel.

 

[alansidman]

Looks like the error message says you are missing a closed curly bracket.

 

[Skip2MiLu]

Looks like the error message says you are missing a closed curly bracket.

Hi Alansidman

Sorry to be a bother, but I adjusted the formula with relevant colum data and expended the column, but it not doing the same as SUMIFS in the example I sent above.
Also previously loading this Country Variety Sales query resulted in about 4000 records, however it wanted to load 30 000+ records after I expanded the column from this SUMIFS calculated column.
So somewhere something is not working they way I wanted.

I not sure if you have any other ideas. Otherwise I will just need to do it the long way.