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

[ebuchholz]

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.

[v-jiascu-msft]

Hi @ebuchholz,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[ebuchholz]

Resource	Department	Resource Supply
Resource A	Dept 1	144
Resource B	Dept 2	924
Resource C	Dept 3	888
Resource D	Dept 4	60
Resource E	Dept 4	204

Project Name	Estimated Hours	Percentage contribution	Resource	Hours Demand
Project A	100	0.05	Resource A	5
Project A	100	0.05	Resource B	5
Project A	100	0.1	Resource C	10
Project A	100	0.5	Resource D	50
Project A	100	0.3	Resource E	30

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.

[v-jiascu-msft · 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])

Best Regards,

Dale

[Anonymous]

Hi @ebuchholz

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

Thanks

Raj