Sum minus value from related table

[jsangerman]

I have two tables:

People

Person	Capacity
John	1
Mike	0.8
Alice	0.4

Utilization

Person	Period	Project	Utilization
John	10/1/2021	Project 1	0.5
John	10/1/2021	Project 2	0.4
John	11/1/2021	Project 1	1
Alice	10/1/2021	Project 2	0.5

I'm trying to calculate the available capacity for each person (rows) for each period (columns), equal to the capacity of the person minus the sum of the utilization for the period. The result I'm looking for is this table:

Person	10/1/2021	11/1/2021
John	0.1	0
Alice	-0.1	0.4
Mike	0.8	0.8

What DAX formula gets me there?

[amitchandak · Accepted solution]

@jsangerman , Try one of the two measures

Sum(people[Capacity]) - sum(Utilization[Utilization])

or

Sumx(summarize(Utilization, people[person], people[Capacity], Utilization[Period], "_1", sum(Utilization[Utilization])), [Capacity] -[_1])

[jsangerman]

The second option is exactly what I needed, and I understand how it works. You're using SUMMARIZE to create a new table with 1 value for Capacity plus the sum of Utilization for a given person and period. The math is simple from there.

Thank you.