Assign Random data to Executive with limited proportion

[vishwa1979]

Dear Team,

I want to distribute a random percentage of cases to each executive , not just random assignment. That means I want to control the proportion of cases each executive gets.

Excel is attached

Executive Name - Sample % sheet

Data - Sub sheet

Thanks in advance

[jrlindem]

Greetings!

I think you could approach this by using total record count, then you could compute how many records go to each Executive.  The randomizing could be accomplished with a formula function RAND() which assigns a random number between 0 and 1.  Then just sort on that number in ASC or DESC to force that randomization.

From here, either and iterative macro to assign the records or you could probably just use a formula tool to assign records based on how many there should be from your previous percent of total record counts.

I'll see if I can work up a mock, a little later.  For now, I hope this gets you started.  -Jay

[jrlindem]

@vishwa1979 

Doesn't look like anyone else has jumped in here yet.  I built out an illustrative workflow to help you along the way.  This is avoiding macro's, but still does the trick:

I used the technique I described in my previous post where I took the total number of records and used that to, along with the percentages you provided, and translated that into the amount per Executive.  I then created a running total of those values, to create the threshold of records that needed to be assigned to each.

I chose a starting number that fit neatly with your percentages, but if your percentages create partial results (not whole numbers) you’ll need to decide how you want to handle that.  You can have half a record assigned to two different Executives!  Use Floor or Ceiling to help with this.

Also, this solution is predicated on the idea that you don’t have that many Executive partitions and that it is reasonable to brute-force the formulas to accommodate the partitions.  If you have a ton more partitions, then I would recommend you explore an iterative macros instead to assign records until the quantity is fulfilled from the first executive, then moving on to the next, etc.

So long as your list is short, then this should work just fine!

Hope this helps, sample workflow is attached, -Jay

[WirkKarl]

You could approach this by calculating the proportion of cases each executive should receive first and then using a randomization step within those limits. For example, in Alteryx, you might use a Generate Rows or Random % tool combined with a Join to assign cases based on the target percentages, ensuring the total matches your desired distribution. It’s basically controlling the randomness within predefined allocation rules rather than letting it be fully uniform.