Calculated Column to aggregate results from various tables

[clubspec]

Hi Gurus,

I have three tables (Inventory, BOM, Work Orders) and I am trying to build a "Forecast Qty on Hand" column inside the Inventory table.
For example, based on Work Orders Table I have an order to make 10 of Table Lamp; and according to BOM table it requires 10 times Raw Material Qty listed on the BOM, and therefore my Calculated Column in Inventory Table will have Qty on Hand of Raw Material minus the Qty consumed in the Work Orders.

Is this easy to achieve?  The challenge is BOM table has multiple Required Material for a Finished Goods.

Appreciate your help in advance, I hope I can explain myself better.

Thanks,

Ray

[MFelix · Accepted solution]

Hi @clubspec ,

To what I can understand you want to calculate the total inventory after the work orders correct? 

So for the finish goods you will have an increase of 10 each and for the raw materials a decrease.

Make the following setup:

• Relationships:
  • Inventory[Item n] 1 -> BOM[Required Material] *
  • Inventory[Item n] 1 -> Work Order [Item Nº] * (INACTIVE RELATIONSHIP)
  • Work Order [Item Nº] * ->BOM[Required Material] *
• Then add the following measure:

Forecast = 
SUM ( Inventory[Qty on Hand] )
    + CALCULATE (
        SUM ( 'Work Order'[Order Qty] );
        USERELATIONSHIP ( Inventory[Item Nº]; 'Work Order'[Item Nº] )
    )
    - SUMX (
        ADDCOLUMNS (
            BOM;
            "@orderQty"; SUMX (
                FILTER ( 'Work Order'; 'Work Order'[Item Nº] = BOM[Finish Good] );
                'Work Order'[Order Qty]
            ) * BOM[Require Qty]
        );
        [@orderQty]
    )

Check PBIX file attach.

[clubspec]

Thank you so much @MFelix 

I appreciate your instructions and the model!