cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ebuchholz Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

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

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.
4 REPLIES 4
Super User
Super User

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

Hi @ebuchholz

 

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

 

Thanks

Raj

ebuchholz Frequent Visitor
Frequent Visitor

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

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.

Community Support Team
Community Support Team

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

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.
Community Support Team
Community Support Team

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

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.