Power Pivot - Add calculated column to subtract total from another table

[UniqueUsername]

I'm slowly tinkering on a project at work that keeps pushing me deeper and deeper into Excel. This is my first venture into Power Pivot and need a hand getting started.

I have 4 tables, 2 containing unique values that tie the other 2 together.

UNIQUE CUSTOMER - just a list of unique customers, relationships have been created for PLANNED/RECEIVED
UNIQUE MATERIAL - just a list of unique materials, relationships have been created for PLANNED/RECEIVED
PLANNED ORDERS - contains CUSTOMER, MATERIAL & QTY (does not contain multiples of CUSTOMER/MATERIAL combinations)
RECEIVED ORDERS - contains CUSTOMER, MATERIAL & QTY (can contain multiples of CUSTOMER/MATERIAL combinations)

I'm trying to create a column that calculates the difference between what we had planned for a customer to order, and what they have ordered so far.

On PLANNED ORDERS, I'm trying to create a calculated columns that would sum all orders received for the specific CUSTOMER/MATERIAL combination on a row.

Best I can figure, I need to combine CALCULATE and SUMX but am struggling to get there.

=CALCULATE(SUM(receivedQTY),planCUSTOMER=receivedCUSTOMER, planMATERIAL=receivedMATERIAL)-planQTY

So if CUSTOMER123 purchased 23 units of MATERIALXYZ and we had planned for them to purchase 30 units of MATERIALXYZ the result would be -7

I'm sorry for how lost I am

 

[RoryA]

Is there a particular reason you want this as a calculated column rather than a measure? How are you planning to use it?

 

[UniqueUsername]

Because I didn't know any better haha.

I had incorrectly assumed that because I couldn't add a calculated field all willy nilly like usual, that I needed to dig deeper.

Thank you for pointing me in the right direction.