Trying to do some allocation by priority. Example. I have Product as a Parent and Customer as its child. I have 100 units of product A and 3 customers. Customer1 has a demand of 50 and is first priority so he gets all 50 he needs, customer 2 has demand of 40 and is second priority. I have 50 units left after customer 1 so customer 2 gets his 40. Customer 3 needs 30 and is 3rd priority. I have 10 units left so customer 3 gets shorted 20 units. The challenge is subtracting from the total 100 units as it does down through prioritized hierarchy list.
I have an idea of how to do this, but it's turning out to be less than scaleable... wondering if any of you have other ideas.
Probably one of the quickest self-solved forum questions lol. Just needed to type it out I guess.
With the help of @johndorma, I think we've solved this by calculating the % demand for each customer, then using RANKCUMULATE on that % to then calculate how much demand each customer is allocated. RANKCUMULATE is limited to 10 million cells, but that should be fine for my model.