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

[elliotdixon]

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.

         

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

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

[karthik]

@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 · Accepted solution]

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

 

Regards,

Charlie Liao

[elliotdixon]

Hi @v-caliao-msft

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

Thanks heaps.