Forum Discussion
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.
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
4 Replies
Hi ebuchholz
Can you post some sample data with expected output ( in copy & pasteable format)?
Thanks
Raj
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-msftMicrosoft Employee
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
- v-jiascu-msft
