Assigning rows equally across a list

[sarawilson]

Hello!

I'd like to be able to equally distribute records in a table to names from a list. I'm able to assign them randomly with:

AssignedEmployee = LOOKUPVALUE(organizationalPerson[displayName],organizationalPerson[EmployeeOrder],RANDBETWEEN(1,[EmployeeCount]))

but I need to be able to equally distribute rows across staff (some days there may be 60 rows, some days 20, etc.). I've tried messing around with RANKX and LOOKUPVALUE but can't get it to make sense. Any help would be greatly appreciated!!

While I have sensitive data and can't share a file, the end goal would be something like below. 

Client	Employee
1	A
2	A
3	A
4	B
5	B
6	B
......	....
31	C
32	C
33	C

[djerro123 · Accepted solution]

Your usecase intrigued me. It seems you are trying to create some sort of daily roster? That is an interesting use of Power BI. 

Anyway, onto your question. What I came up with was the following.

I assume there is a table of (unique) Clients, as well a table of (unique) Employees. You want to create a calculated column in the Clients table that assigns an Employee to each Client row, in such a manner that every Employee occurs roughly the same ammount of times in the Clients table.

I started off with two tables, one Client Table and one Employee Table (note the Index columns! i've added those in the Query Editor!) There are 31 rows in Client table, and 5 in Employee table.

Example table Client (N=31)Example table Employee (N=5)

Now we are going to add a calculated column to the Client table in which we are going to assign employee ID's to Clients (evenly distributed):

AssignedEmplID = 
VAR _clientsPerEmp = DIVIDE(COUNTA(TableClient[Client]), COUNTA(TableEmpl[Employee]))
RETURN
ROUNDUP(DIVIDE(TableClient[Index], _clientsPerEmp), 0)

What we are doing here is calculating how many clients per employee should be assigned (divide the number clients by the number of employees). Then we divide the index of that client (let's say, 4) by that number (in our case 31 divided by 5). Round that up to the nearest whole number and that is the assigned Employee Index. Consequently; the fifth employee get's 7 clients, all other employees get 6. 

Last step is to create a relationship between Employee Index and AssignedEmployee (1-to-many), so you can use names instead of indexes in your visuals, for example: 

Clients with AssignedEmplID and (by relationship) the Employee name

Please mark this answer as solution if this answered your question. 🙂

[sarawilson]

AWESOME! That's just what I needed, thanks so much!