Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
anil_kathane
Regular Visitor

How to use SUMX across multiple tables

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_1-1668065480058.png

 

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @anil_kathane 

please try this measure

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

vxiaotang_0-1668680214611.png

 

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.

View solution in original post

4 REPLIES 4
anil_kathane
Regular Visitor

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
Community Support
Community Support

Hi @anil_kathane 

please try this measure

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

vxiaotang_0-1668680214611.png

 

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
Solution Sage
Solution Sage

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.

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.