SUMIF in Query Editor on Power BI Desktop?

[jjbates]

I am trying to do a simple sumif in the Query Editor of the PowerBI desktop. I cannot edit the source data, so I need a calculated column as a new column that simply sums one column if it matches the values in another. I have done this with DAX before in Power Excel using this formula: CALCULATE(SUM([Received Qty]),FILTER('sheet 1',[Location]=EARLIER([Location])

and need something similar in the M language. Does this exist? Please help.

[Anonymous]

All the solutions I find point to doing a "Group By" in power query. However, there are many situations where every row is unique, but they often share a common identifier that needs a sum-if. For example, I can have a list of employees (All Unique). Each employee has a wage and a department. I want my end result to show the sum of wages related to the department as a percent. I don't want to group on department, because I will not be able to see the employee with their percentage of earnings as it relates to the department. A simple sum-if would easily do this. Does anyone have a working solution?  See table below. That's the output I'm looking for from Power Query.

Employee	Departmetn	Wages	Department Wages	% of Department
Charles	Sales	50,000.00	150,000.00	33%
Kristi	Sales	40,000.00	150,000.00	27%
Mary	Sales	60,000.00	150,000.00	40%
Bob	Service	65,000.00	194,000.00	34%
Bill	Service	62,000.00	194,000.00	32%
Isaac	Service	67,000.00	194,000.00	35%
Charles	Parts	87,000.00	269,000.00	32%
Wendy	Parts	45,000.00	269,000.00	17%
Sam	Parts	73,000.00	269,000.00	27%
Aaron	Parts	64,000.00	269,000.00	24%
Wayne	Finance	84,000.00	180,000.00	47%
Jodi	Finance	53,000.00	180,000.00	29%
James	Finance	43,000.00	180,000.00	24%

[v-qiuyu-msft · Accepted solution]

Hi @jjbates,

You can open Query Editor, use Group By feature:

The backend M formula is below:

= Table.Group(dbo_DimSalesTerritory, {"SalesTerritoryGroup"}, {{"Total", each List.Sum([SalesTerritoryKey]), type number}})

Best Regards,
Qiuyun Yu

[MarcelBeug]

For example the sum of all values where Group = "A":

= List.Sum(Table.SelectRows(PreviousStep, each [Group]="A")[Value])

[JohnSonn]

Hello Marcel,

I am trying your formula but I cannot get it to work.

I am doing something a little more complicated:

= Table.Group(#"Removed Errors", {"order no."}, {{"Adj Gross Value", List.Sum(Table.SelectRows(PreviousStep, each Text.Start([ReferenceID], 3) = "ADJ") [Gross Value]), type number}}

As you can see I want to match using Text.Start but I cannot get it to work. This is my error:

Expression.Error: The name 'PreviousStep' wasn't recognized. Make sure it's spelled correctly.

If you can help thanks in advance.