Group entries based on defined parameters

[iammrishoabeid]

Hi Power BI Community,

I have an Excel spreadsheet with some information that needs to be grouped following a certain a logic would appreciate any advice any of you might have. The data is structured as follows:

1. First Name
2. Last Name
3. Email (this is a unique field)
4. Sex (contains values either Male of Female)
5. Nationality
6. Consultancy (contains values either Yes or No)
7. A list of 26 columns containing names of companies which all contain numeric values from 1 to 26

What I need to achieve is:

• I need to group the responses by Company
• Where each group must contain at least 1 response with Yes in Consultancy
• Each group must have an even number of responses or a maximum of 12 per group
• Each group must have a mix of Sexes
• Each group must have a mix of Nationalities

So, for instance: if the file contains 279 responses, then:

1. groups will be made based on the Company
2. 22 groups of 12 and 1 group with 23 or 24
3. each group will have at least 1 person with Consultancy experience (where consultancy is Yes for the given response)
4. each group will have a mix of males and females based on the Sex column, and a mix of nationalities.

How can I achieve this in Power BI?

I really appreciate any support or advice you may be able to offer.

Thanks in advance.

SAMPLE DATA.
The sample data file contains a record of 43 responses in the first sheet named "Sample Data" and 3 examples of outputs in the second sheet named "Desired Output" (also see the image attached below).

Ideally speaking, the actual data size will have hundreds of responses, in this iteration there are 279 responses. Based on these, we want to group each company to have about 11 to 12 users based on their responses. We first consider their top 5 choices of companies, and if we cannot use those due to demand or something else, then we consider the rest of their choices.

Example Output: based on 43 responses with each company having 5 people. The last column called "Preference" is the company name and choice in the first sheet with sample data. In this case, for the first group in Company 1, all users with numbers in Preference set those choices as their preference. And so on...

 

[ryan_mayu]

pls provide some sample data and expected output

[iammrishoabeid]

Hi @ryan_mayu, thanks for getting back to me so promptly. I have attached a link to a Google Sheet with sample data and the desired output. I have also included a screenshot of the desired about in the inquiry. I hope that helps.

Thanks again for any support or advice you can offer.

[ryan_mayu]

still not clear about the logic of data selecting.

why we select user 1, 10,15, 16 and 19 for company 1?

[iammrishoabeid]

Sorry for the confusion.

I set the example randomly but the logic should check the first 5 choices of a user when assigning them to a company group. For instance, in Company 1, if we were grouping each to company to have 10 users, then for all 10 users to be added in that company group would have selected Company 1 as their top 5 choice. Where this is not possible, for instance, Company one is very popular and almost all users select it in their top 5, then we consider choices 6 to 26 for each individual user and group them that way.

Once a company has the maximum selection of the number of required people, while considering each group has at least 1 people with Consultancy experience, and a mix between males and females, and nationalities, they we continue grouping the other users in the next groups.

As such, if all users who set Company 1 as their first choice were female and or from the same nationalities, and or all or none had Consultancy experience, the algorithm should select other users in the pool of responses, whether or not they selected Company 1 as their top 5, provided they can introduce a mix of people in the group. Where we must have at least 1 Consultant, some males and females, and from different nationalities.

Does that help? 

[ryan_mayu]

still not clear about the logic. it looks like that the result was randomly to some extent? All 43 users have the number for company 1, so I still don't under why you pick up those 5 users as the result.