Prioritized Allocation


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.

  • Hi Cam_Haines,


    Please apply DISCO, and create a 'Customer Properties' module to capture the Parent of 'Customer'.


    Please try the following.

    Screen Shot 2019-03-13 at 4.21.26 PM.pngScreen Shot 2019-03-13 at 4.21.36 PM.pngScreen Shot 2019-03-13 at 4.21.44 PM.png



  • Hello

    You could also use the optimizer

    The variables would be the quatity to assign to each customer (A, B and C)

    You could assign to each customer a property wich is the priority: such as priority 1 will get the highest weight

    so in this example, a will be assigned the weight of 3, B the weight of 2 and C the weight of 1

    the objective function will then be maximize weight * Variable

    The constraint is sum of variable <= to the available quatity (100 in this example). we could write A+B+C=100 but it is safer to write <= to avoid any errors

    Variable <= Demand (in your example A<= 50,...)

    I guess your solution works too, but this one alternative for the ones who are fond of the optimizer