Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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.
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.
Thank you very much! That did the trick. I don't know why I didn't thought of that earlier. I spent the last two hours trying to build the same table with DAX functions.
@mork As an alternative you can create a "New Table" (assuming you are using latest DAX versions ) and then enter more calculated column.
SumProjects = ADDCOLUMNS ( SUMMARIZE ( Projects; Projects[Project] ); "Actual Time"; CALCULATE ( SUM ( Projects[Timesheet] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
74 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |