Forum Discussion
SUMIF in Query Editor on Power BI Desktop?
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.
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
4 Replies
v-qiuyu-msftCommunity Support
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
Community Champion
For example the sum of all values where Group = "A":
= List.Sum(Table.SelectRows(PreviousStep, each [Group]="A")[Value])
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.
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%
