Forum Discussion
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:
| Client | Employee |
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | B |
| 5 | B |
| 6 | B |
| ...... | .... |
| 31 | C |
| 32 | C |
| 33 | C |
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. 🙂
3 Replies
- JarroVGIT
Resident Rockstar
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. 🙂
AWESOME! That's just what I needed, thanks so much!
Hello! I've got a similar challenge which this solution helped me a lot already, however, I've got some criteria/rules:
instead of Clients and Employees - I have users and auditors - which the auditor can't audit themself (so the auditor needs to be different from the user) but always considering the same number of rows for every auditor.Could you please help?? Thank you 🙂
