cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sarawilson Frequent Visitor
Frequent Visitor

Assigning rows equally across a list

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. 
 
ClientEmployee
1A
2A
3A
4B
5B
6B
..........
31C
32C
33C

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
djerro123 Member
Member

Re: Assigning rows equally across a list

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.

 

image.pngExample table Client (N=31)image.pngExample 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: 

image.pngClients with AssignedEmplID and (by relationship) the Employee name

 

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

 

View solution in original post

2 REPLIES 2
djerro123 Member
Member

Re: Assigning rows equally across a list

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.

 

image.pngExample table Client (N=31)image.pngExample 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: 

image.pngClients with AssignedEmplID and (by relationship) the Employee name

 

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

 

View solution in original post

sarawilson Frequent Visitor
Frequent Visitor

Re: Assigning rows equally across a list

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,316)