Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
95 | |
80 | |
77 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |