How do I calculate a new column in power bi that sums the values of a column from Table1 then subtracts the sum of groups of rows in Table 2?

Question

I'm new to Power BI and struggling with writing DAX to define a new column for what seems like it should be a simple subtraction exercise. Here's a simulation of my data tables:

Resource	Cpty Type 1	Cpty Type 2	Cpty Type 3	Cpty Type 4
Bulldozer	10	50	0	0
Dump Truck	150	150	150	150
Backhoe	25	0	50	50

Project	Work Type	Resource	Usage Hours
Apple	Type 1	Bulldozer	8
Apple	Type 2	Dump Truck	100
Peach	Type 3	Dump Truck	50
Pear	Type 4	Backhoe	42
Pear	Type 1	Compactor	98
Pear	Type 3	Excavator	80
Plum	Type 2	Dump Truck	50
Plum	Type 1	Dump Truck	25
Pineapple	Type 4	Dump Truck	75

I provide a dashboard for each resource by slicing/filtering on the resource. Table 1 has a single row for each resource with multiple capacity type columns that need to be added together to get the [Capacity] for the resource.

Table 2 has the detailed consumption data that shows a project, type of work, the resource and the hours consumed. I want subtotals of usage hours for each group of [resource and work type usage] to show in cards on the dashboard, then I need to show the over/under utilization by taking the summed [Capacity] for the resource from Table 1 and subtracting the sum of each of the [resource and work type usage]for that resource from Table 2.

Using the data example, on a report for the Dump Truck:

• the Capacity card should show 600 hours
• the Work Type 1 card should show 25 hours
• the Work Type 2 card should show 150 hours
• the Work Type 3 card should show 50 hours
• the Work Type 4 card should show 75 hours
• the Utilization card should show under-utilization of 300 hours.

I've fiddled with CALCULATE statements and SUM and SUMX but can't get it right.

I was expecting to be able to take a SUM from Table 1 and subtract a SUMX from Table 2, with a slicer/filter applied at the page level. I've tried putting this new column in both Table1 and Table 2, which are joined on [Resource]. The DAX builder doesn't let me choose data from the other table when attempting the calculation.

Help greatly appreciated!

Answer 1

in the first table, select the first column and unpivot other column in PQ

2. create a dim table

dim_table = DISTINCT('Table'[Resource])

3. create realtionships among three tables

4. then you can use filter to switch the output from different resources