SUM rows

[mork]

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?

[SabineOussi]

Hi mork,

You'll have to use this measure:
Measure = SUMX(VALUES(Table1[ProjectName]),CALCULATE(SUM(Table1[TimesheetActualWork])))

Does it work?

[mork]

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.

[Mi2n · Accepted 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.

[mork]

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.

[konstantinos]

@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] ) )
)