Forum Discussion
SUM rows
I have a table in the power query that among other columns has the following.
ProjectName ResourceName TimesheetActualWork Week
a John 8 1
a John 5 2
a George 8 1
b John 3 2
b George 8 2
etc etc.
Basically its a table with each input from the timesheets, per resource per project per week.
What I want to do is to create a table containing only the sum of the actual work per project of all resources of all weeks. A table that would look like bellow.
ProjectName ActualWork
a 21
b 11
etc etc.
How can I achieve that?
mork, in the Query editor window (Power Query) right click on your query/table and select Duplicate or Reference. This will create another table with the same contents (I would prefer Reference).
In this new query, select "Group by" from the Home toolbar. In the Group by window, Select the Project Name field form the dropdown for Group by section. Enter "Actual Work" in the New column name text field and select the operation as "Sum" and the Column as "TimesheetActualWork". Click OK once done.
This should provide the desired table.
5 Replies
Hi mork,
You'll have to use this measure:
Measure = SUMX(VALUES(Table1[ProjectName]),CALCULATE(SUM(Table1[TimesheetActualWork])))Does it work?
It works but I don't want to use a measure. I want the data in a table in order to add more related columns after. I want to do that either in the power query or using a new table and DAX functions.
mork, in the Query editor window (Power Query) right click on your query/table and select Duplicate or Reference. This will create another table with the same contents (I would prefer Reference).
In this new query, select "Group by" from the Home toolbar. In the Group by window, Select the Project Name field form the dropdown for Group by section. Enter "Actual Work" in the New column name text field and select the operation as "Sum" and the Column as "TimesheetActualWork". Click OK once done.
This should provide the desired table.
