Fact table - Sales Lines, linked to lookup Item table with Quantity on Hand (QOH) value in column b. Pivottable shows all sales lines grouped by order. Item A is in Order 1 with a quantity of 5, Item A is in Order B with a quantity of 5. QOH is 5, therefore both orders are short, even though there is enough to fill one. Item B and C are on both orders, but there is plenty on hand. The end result in the pivot table should show both orders with only Item A as rows. Is this possible? I've got over 30 hours into this and have yet to get it to work correctly.
My last thought is to create a separate table of Items short and use a value of 1 if short and 0 if not and link that table to the sales lines, then calculate sales lines on only 1s. Will need to give that a try.
Appreciate any suggestions.
My last thought is to create a separate table of Items short and use a value of 1 if short and 0 if not and link that table to the sales lines, then calculate sales lines on only 1s. Will need to give that a try.
Appreciate any suggestions.
