Adding from multiple datasets

[nhwmpbi]

I have one dataset called Master Project Information. I'm trying to pull information out of other data sources.

Here's what I'm hoping the end result looks like-

Master Project Info

Project Number	Project Title	Sales Hours	Sales Cost	Support Hours	Support Cost
10123	Test Project	6	(sales hours * $120)	8	(support hours * $130)

I have other datasets for various employees where they add their time, those look something like the tables below. I want to add and sum the hours based on the Project ID# but I'm having trouble doing so. 

Sales Team Table

Work #	Date Work Performed	Hours	Status
10123	2/27/23	5	In Progress
10122	2/26/23	8	Complete
10123	2/26/23	1	In Progress

Support Team Table

Work #	Date Work Performed	Hours	Status
10125	2/27/23	8	Complete
10124	2/26/23	8	Complete

Looking for some advice on how to best go about this. Thanks

[v-zhangti]

Hi, @nhwmpbi 

Based on the data you provide, you can try the following.
Measure:

Sales Hours = CALCULATE(SUM('Sales Team Table'[Hours]),ALLEXCEPT('Sales Team Table','Sales Team Table'[Work #]))

Sales Cost = [Sales Hours]*120

Support Hours = CALCULATE(SUM('Support Team Table'[Hours]),ALLEXCEPT('Support Team Table','Support Team Table'[Work #]))

Support Cost = [Support Hours]*130

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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