Inventory Allocation to Work Order in DAX

Question

Hello everyone,I am looking for a help to guide me with inventory availability check for work orders before making pick-list. I would like to see eligible work orders as per On Hand Qty.&nbsp;&nbsp;I have attached a snapshot for reference.I have been at it for a few days and I am unable to crack the logic.&nbsp;Would be grateful for any suggestions on how to implement the logic.....

viren2023 · Answer

&nbsp;BOM (Bill of Materials)&nbsp;Inventory&nbsp;Work Orders&nbsp;Inventory Check&nbsp;QuickViewParent PartChild PartQty Per&nbsp;Item NoQty On Hand&nbsp;WorkOrder NoParent PartWork OrderQty Open&nbsp;Work Order NoParent PartChild PartQty PerWork OrderQty OpenInventoryQry RequiredAvail On Hand INVQty after allocationWork OrderQty AllocatedStatus&nbsp;WorkOrder NoWork OrderQty OpenWork OrderQty Allocated1111112&nbsp;1110&nbsp;W00111113&nbsp;W001111111236243TRUE&nbsp;W001331111121&nbsp;1110&nbsp;W00222224&nbsp;W001111112133123TRUE&nbsp;W0024411111310&nbsp;1110&nbsp;W00311111&nbsp;W00111111310330403TRUE&nbsp;W003112222215&nbsp;1210&nbsp;W00433335&nbsp;W0022222215420104TRUE&nbsp;W004552222223&nbsp;125&nbsp;W00533333&nbsp;W002222222341284TRUE&nbsp;W005312222114&nbsp;1350&nbsp;W00611112&nbsp;W003111111212221TRUE&nbsp;W006202222243&nbsp;1320&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W003111112111111TRUE&nbsp;&nbsp;&nbsp;&nbsp;3333312&nbsp;2120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W00311111310110301TRUE&nbsp;&nbsp;&nbsp;&nbsp;33331120&nbsp;2110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W0043333312510555TRUE&nbsp;&nbsp;&nbsp;&nbsp;4444411&nbsp;2220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W004333311452045TRUE&nbsp;&nbsp;&nbsp;&nbsp;4444113&nbsp;2415&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W005333331236493TRUE&nbsp;&nbsp;&nbsp;&nbsp;4444432&nbsp;3120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W0053333114312-81FALSE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W006111111224-120FALSE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W006111112122102TRUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3115&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;W00611111310220202TRUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3210&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;415&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;435&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp; viren2023&nbsp;,
&nbsp;
Not quite sure what you mean, is the expected result you want to arrive at all of the graphs above or a particular graph, what are the data used to arrive at the expected result, and what is the logic used to arrive at the result?
&nbsp;
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

viren2023 · Answer

Hi&nbsp;Anonymous&nbsp;I would like to generate "Inventory Check" and "Quick View" structure based on "BOM", "Inventory", and "Work Orders" tables.

ashish_mathur · Answer

Hi,
Share the download link of the MS Excel file.&nbsp; In another tab of the Excel file, show the expected result with your formulas/Pivot Table already written/built there.&nbsp; I will try to translate the Excel formula logic into the DAX language.

Forum Discussion

Inventory Allocation to Work Order in DAX

4 Replies

Related content

Power Query - Inventory allocation

Cost allocation

Map Inventory ID to multiple sales order - CC/Measure (looping or sequential allocation)

Inventory and Stock balance calculation

Inventory Forecast

BOM (Bill of Materials)		Inventory	Work Orders		Inventory Check		QuickView
Parent Part	Child Part	Qty Per	Item No	Qty On Hand		WorkOrder No	Parent Part	Work Order Qty Open	Work Order No	Parent Part	Child Part	Qty Per	Work Order Qty Open	Inventory Qry Required	Avail On Hand INV Qty after allocation	Work Order Qty Allocated	Status	WorkOrder No	Work Order Qty Open	Work Order Qty Allocated
1111	11	2	11	10		W001	1111	3	W001	1111	11	2	3	6	24	3	TRUE	W001	3	3
1111	12	1	11	10		W002	2222	4	W001	1111	12	1	3	3	12	3	TRUE	W002	4	4
1111	13	10	11	10		W003	1111	1	W001	1111	13	10	3	30	40	3	TRUE	W003	1	1
2222	21	5	12	10		W004	3333	5	W002	2222	21	5	4	20	10	4	TRUE	W004	5	5
2222	22	3	12	5		W005	3333	3	W002	2222	22	3	4	12	8	4	TRUE	W005	3	1
2222	11	4	13	50		W006	1111	2	W003	1111	11	2	1	2	22	1	TRUE	W006	2	0
2222	24	3	13	20					W003	1111	12	1	1	1	11	1	TRUE
3333	31	2	21	20					W003	1111	13	10	1	10	30	1	TRUE
3333	11	20	21	10					W004	3333	31	2	5	10	55	5	TRUE
4444	41	1	22	20					W004	3333	11	4	5	20	4	5	TRUE
4444	11	3	24	15					W005	3333	31	2	3	6	49	3	TRUE
4444	43	2	31	20					W005	3333	11	4	3	12	-8	1	FALSE
			31	10					W006	1111	11	2	2	4	-12	0	FALSE
			31	20					W006	1111	12	1	2	2	10	2	TRUE
			31	15					W006	1111	13	10	2	20	20	2	TRUE
			32	10
			41	5
			43	5