I have a parts table ("Parts" - key is Part Number) for a marine services shop, and would like to create a column documenting current inventory from two sources - a "Supplier Invoice Detail" table which logs supply of parts into the shop, and a "Work Order Detail" table which logs uses, or decrements to inventory as parts are used. Both detail tables carry "Part Number".
I have One-to-Many relationships between "Parts" and the two detail tables, so was expecting the Filter context to be carried into the detail tables and thus return the sum of Qty from just the rows containing the current Part Number.
Current Inventory = (SUM('Invoice Line Item'[Qty]))-(SUM('Work Order Line Item'[Qty]))
I know my problem is with the filter context not working as I envision, but am at a loss and would appreciate any guidance.
Thanks.
