I've been asked to build a report for my company that will improve our order processing by making an at-a-glance report as to which orders should be accepted or rejected based on Ordered Quantity and what we have in stock.
The most obvious solution is to simply build a conditional column which compares the ordered amount to our on-hand quantity, but this clearly presents a problem where we have a very low OH amount (lets say 4) and numerous low-quantity orders (say 7 or 8 orders of quantities of 2 or 3 items).
I'm still looking into the logic of how to essentially sum orders by itemCode, but I'm afraid I've got two bits of logic I'm struggling with - Even summing all orders by item code returns a reject for all orders if the total is over our OH quantity. I need to be able to look at these orders, by item code, line by line and accept all the orders that I can before rejecting those that go over our stock.
Additionally, in productivity's interest, I want to accept the highest quantity orders first before accepting smaller orders. Is this something that can be done in Power Query? Any assistance is much appreciated.
