Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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% |
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
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |