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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ebuchholz
Frequent Visitor

Sum Column in one table from column in another table based on other columns

I am putting together an analysis of supply and demand for resource planning, and am having a difficult time getting the view I would like.

 

 Table A contains resource, year, month, and hours. Table B also contains resource, year, month and hours. 

 

I would like to SUM Table A hours for each resource, year and month, and subtract that from SUM table B hours for each resource year and month. Table A may contain many values for each resource and month, while table B will contain 1 value for each resource and month.

1 ACCEPTED SOLUTION

Hi @ebuchholz,

 

Please check out the demo in the attachment. 

Seems you just need to establish a relationship using [Resource] and create a measure.

Measure = sum(Table1[Resource Supply]) - sum(Table2[Hours Demand])

Sum_Column_in_one_table_from_column_in_another_table_based_on_other_columns

 

Best Regards,

Dale

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

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

Hi @ebuchholz,

 

Could you please mark the proper answers as solutions?

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ebuchholz
Frequent Visitor

ResourceDepartmentResource Supply
Resource ADept 1144
Resource BDept 2924
Resource CDept 3888
Resource DDept 460
Resource EDept 4204



Project NameEstimated HoursPercentage contributionResourceHours Demand
Project A1000.05Resource A5
Project A1000.05Resource B5
Project A1000.1Resource C10
Project A1000.5Resource D50
Project A1000.3Resource E30

 

 

The output I want is a stacked barchart with the project name as the legend and each resource being the axis to show total supply for each resource minus demand for each resource, and in some causes, that bar will go negative because there is more demand than supply.

Hi @ebuchholz,

 

Please check out the demo in the attachment. 

Seems you just need to establish a relationship using [Resource] and create a measure.

Measure = sum(Table1[Resource Supply]) - sum(Table2[Hours Demand])

Sum_Column_in_one_table_from_column_in_another_table_based_on_other_columns

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @ebuchholz

 

Can you post some sample data with expected output ( in copy & pasteable format)?

 

Thanks

Raj

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.