How to use SUMX across multiple tables

[anil_kathane]

I am trying to understand how to colelct the effort data for a project. I have two tables: Tasks and Sub-Tasks. Each sub-task is linked to a Task with the Task Key. We have effort for tasks as well as Sub-Tasks. Total effort for each tasks is the effort for the task and effort for all subtasks. Below are the detials:

Table1: Tasks

Table 2: SubTasks

We need to get the below output:
Table 3: Expected Output

An intermetidate step could be:
Table 4: Intermediate Step

How to achieve this result. I believe this should be very simple. But I am unable to get sum from Sub-Task level based on the relationship between the two tables. the SUMX funtion doesn't allow comparison among columnf from two tables. Any suggestions plase?

[anil_kathane]

Thanks everyone for your responses. I guess I wasn't clear in explaining my problem. I need to update this value as a computed column in the tables. Reason is, I have multiple levels of tables and I need to gradually move data up from bottom most layer table upwards to top mosts layer table (it will be hierarcy of 4-5 lelvels). And then I have to use this data in combination with other data for further analysis. As such, I need computed columns; measure may not work in my situation.

[v-xiaotang · Accepted solution]

Hi @anil_kathane 

please try this measure

total = MIN(TableA[Effort])+ CALCULATE(SUM(TableB[Effort]),FILTER(TableB,TableB[TaskKey]=MIN(TableA[TaskKey])))

Best Regards,

Community Support Team _Tang

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

[mangaus1111]

Hi @anil_kathane ,

you need a relationship between Task and Subtask tables and then you create this meaure

Measure = 
SUM('Task'[Effort Task]) + SUM('Subtask'[Effort SubTask])

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

[anil_kathane]

@mangaus1111  I tried this, it gives the same value, sum of all effort for every row. Not sure if I am missing anything.