Forum Discussion

nhwmpbi's avatar
nhwmpbi
New Member
3 years ago

Adding from multiple datasets

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 NumberProject TitleSales HoursSales CostSupport HoursSupport Cost
10123Test Project6(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 PerformedHoursStatus
101232/27/235In Progress
101222/26/238Complete
101232/26/231In Progress

 

Support Team Table

Work #Date Work PerformedHoursStatus
101252/27/238Complete
101242/26/238Complete

 

 

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

Need Help

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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.