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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
elliotdixon
Responsive Resident
Responsive Resident

Sumif SumX with related for row level - sum one column if related in another table

Hi

I have two tables and need to a column depending on the related column.

Context is: Trucks do runs and each run can have multiple dockets. Each docket has a weight.

  JobRuns.png        JobRunDocket.png

 

relationship is

One JobRuns[RUN_ID] connects to many JobRunDocket[DOC_RUN_ID]

There can be single or multiple dockets under each run.

 

I want to lookup the DOC_WEIGHT and add the numbers together if there are multiples and then show the data in the JobRuns table

JobRuns what I want.png

 

looking around I think something like sumx(related) might work but just can't get the formula to work.

 

Cheers in advance for all the help.

Rgds - ED

 

 

1 ACCEPTED SOLUTION
v-caliao-msft
Microsoft Employee
Microsoft Employee

Hi ED,

 

According to your description, you need to add a calculated column to dispaly the total WEIGHT for each RUN_ID, right?

 

If that is the case, you can sum up the taotal value based on DOC_RUN_ID IN JobRunDocket table using CALCULATE function, and then use LOOKUPVALUE in JobRuns table.
SumByID = CALCULATE(SUM(JobRunDocket[DOC_WEIGHT]),ALLEXCEPT(JobRunDocket,JobRunDocket[DOC_RUN_ID]))
SumID = LOOKUPVALUE(JobRunDocket[SumByID],JobRunDocket[DOC_RUN_ID],JobRuns[RUN_ID])

 Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

View solution in original post

3 REPLIES 3
karthik
Advocate I
Advocate I

@elliotdixon

 

In your case Job Runs can be a dimension table and Job Run Docket can be a fact table. You can create a relationship between DOC_RUN_ID in JobRunDocket to RUN_ID in JobRuns.

 

Create a new mewasure named Total Run Weight = SUM([DOC_WEIGHT]) in JobRunDocket table in Power BI.

 

You should be able to slice the Total Run Weight by RUN_ID from JobRuns table.

v-caliao-msft
Microsoft Employee
Microsoft Employee

Hi ED,

 

According to your description, you need to add a calculated column to dispaly the total WEIGHT for each RUN_ID, right?

 

If that is the case, you can sum up the taotal value based on DOC_RUN_ID IN JobRunDocket table using CALCULATE function, and then use LOOKUPVALUE in JobRuns table.
SumByID = CALCULATE(SUM(JobRunDocket[DOC_WEIGHT]),ALLEXCEPT(JobRunDocket,JobRunDocket[DOC_RUN_ID]))
SumID = LOOKUPVALUE(JobRunDocket[SumByID],JobRunDocket[DOC_RUN_ID],JobRuns[RUN_ID])

 Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

Hi @v-caliao-msft

Thanks! Works like a charm. Splitting it into two seperate calculations was a great idea.

Thanks heaps.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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