Another table summarize from related tables question

[R_FROM_G]

Hello again 😀

I have 4 related tables: Project --> Milestone --> Task --> TimeTrack.
Project filters Milestone based on [project_id].
Milestone filters Task based on [milestone_id].
Task filters TimeTrack based on [task_id]

In the TimeTrack there is column "time_spent". 

When I put the projectname, milestonename and taskname on the rows in a matrix in my report and then put the time_spent column on the value section of the matrix the total time_spent is calculated correctly on each level of the hierarchy.

I also need the total value spent per project in a summarized table "ProjectSpent".  I thought it would be as simple as the following  code:  SUMMARIZECOLUMNS(Project[id], RELATEDTABLE(Milestone), "Spent", SUM(TimeTrack[DurationInMinutes]))

The results however do not match with values in the matrix in the report.

How can I summarize this correctly?

Thank you very much in advance.

Best regards,

Ruud de Heij

[v-stephen-msft · Accepted solution]

Hi @R_FROM_G ,

To summarize the total time spent per project, you can create a measure in Power BI using the DAX language. Here's a measure that calculates the total time spent for each project:

TotalProjectSpent = 
    SUMX(
        SUMMARIZE(
            TimeTrack,
            Task[task_id],
            Milestone[milestone_id],
            Project[project_id],
            "TimeSpent", SUM(TimeTrack[time_spent])
        ),
        [TimeSpent]
    )

This measure first summarizes the time spent for each unique combination of task_id, milestone_id, and project_id in the TimeTrack table. Then, it sums up the time spent for each project using the SUMX function.

Add this measure to your Power BI report, and you should see the correct total time spent per project.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[R_FROM_G]

That indeed returns the right value. Thank you Stephan 😊  

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.