Power Platform Community Forum Thread Details

Evenly Distribute Records to Users

Hi All,

I've been racking my brain to try and figure out a simple and elegant way to do the following in Power Query: We have claim records that meet a certain criteria that need to be reviewed by two employees each week. The number of records is dynamic and differs each week. The claims are grouped by a customer ID. The number of claims per customer can vary wildly. So a sample file (truncated) might look like this:

CustomerID	ClaimID	Amount
123	A123	50.00
123	A456	75.00
123	A789	90.00
456	A012	100.00
456	A345	80.00

Each weekly file will have dozens of rows. A single customer might have one claim or they could have significantly more - say 25 claims. These claims have to be split as evenly as possible between the two employees each week to review. The challenge is that all records for one customer must stay together, so in the example above all of the records for customer 123 must be assigned to the same employee. I originally tried doing this via a round robin assignment in Power Query, which worked, but often did not give a very even result. Since all claims for one customer must stay grouped together, I would group the claims by customer and then round robin assign each customer (and their associated claims) to each of the two employees. However in an example like the one below, you can see how that might give skewed results:

Customer	CountOfClaims	Employee
123	4	A
456	8	B
789	26	A
012	2	B
345	1	A
678	7	B
901	12	A

In this example, Employee A received 43 claims to review, while Employee B only received 17. A better distribution would have been:

Customer	CountOfClaims	Employee
123	4	A
456	8	B
789	26	A
012	2	B
345	1	B
678	7	B
901	12	B

In this example both employees received 30 claims to review. While I know it won't always be possible to split them evenly, I'd like to get closer to "even" than the round robin example which, since it's random, can give wildly different results for each employee.

Is something like this possible in Power Query? TIA!

Categories:

General Comment

I'd get a sum of the weekly Claims, then divide by the number of Employees. Then, start doling out the largest claim amount, first, to the first employee, then the 2nd largest to the next employee, and keep a running total of how many has been assigned to each employee. (I assume you have more than 2.) Once boarding-school rules have been satisfied. meaning that everyone got their first scoop of gruel, then it's time to start handing out seconds. With that, you'd take the next largest claim number and assign it to the employee with the least amount of assigned claims.

If you want to get fancy, you could do some spot-checks along the way to ensure that it doesn't assign a lopsided percentage prior to the assignment.

Another path would be to determine what the approximate amount each employee should receive, and then grab the highest claim number, then try to find the first value that gets the employee to that number or as close to it. In your example, this would be very simple because the first employee would get 26/50, then the rest would simply be assigned to the 2nd employee.

Lots of ways to skin the cat, but looping could be challenging within PowerApps, since ForAll() is the only option.

Quick Links

Leaderboard