Distribute the equal amount of lines according to criteria

[Spotto]

Hi everyone, I really need some help.
I have a base (EXAMPLE) with a code (PDDMCT) and region (REGIONAL). I need to distribute the amount of table rows by analyst and by region equally.

CONDITIONS
A - The total of lines must be distributed equally to the ANALISTA by REGIONAL

B - If any REGIONAL has less code (PDDMCT) to distribute to the ANALYST, it must be completed with PDDMCT any REGIONAL

--> TABLE BASE

--> TABLE ANALISTA

C- It may happen that two or more analysts have the same REGION, not being able to duplicate the PDDMCT between them.

I made to do the distribution using the row count (table.rowCount), dividing by the number of analysts and allocating by id, but I can't think how it would be to distribute by region for each analyst.

I have attached the file below so they can work on the example given above.

EXAMPLE 

I thank the help of all you.

[lbendlin]

After staring at this for hours I am ready to give up. I believe there is a circular dependency in the requirements. I don't think Power Query or DAX is the right tool for this. It will be trivial to implement in an environment that supports true variables (like Excel VBA, for example).

[lbendlin]

25/3 is not 8, it is 9.  At least one analyst will need to get 9 assignments.

If two or more analysts share a region - are they equally weighted or are there any preferences?  Does Allison get more of MS because it is her only region?

Your sample data is too sparse for the complexity of the ask.

[Spotto]

@lbendlin 25/3 is equal to 8 if rounded down or 9 if rounded up this rule can be any type of rounding

"If two or more analysts share a region - are they equally weighted or are there any preferences?  Does Allison get more of MS because it is her only region?"

--> There are no preferences for the same analysts sharing the same region.
In the example I sent you, ALISSON getting 1 more (MS) or ANAIZA getting 1 more (MS) doesn't matter.

[lbendlin]

In my opinion you cannot round down as that would potentially result in unassigned PDDMCTs.  

It's rather difficult for an algorithm to implement "doesn't matter"  - the random number generator in Power BI is horrible.

[Spotto]

@lbendlin As "it doesn't matter" won't make it easier, we can define some more rules:
1) - Generate an id for each NAME, filling in the REGIONS would start at id = 1 and end at id = 3. In the example it would look like this:

2) Fill the REGION (MS) to ALISSON until the total = 9, if you don't have 9 REGIONS (MS) skip to the next step

3) In the case of ANALIZA that has two REGIONS, find the MS region and fill it in, then find the BA region and fill it in.      
4) Execute the same rule 3) for the other names (id)
5) At the end of all ids, check after distributing the names(id) by REGION if the total by name(id)= 9 and if the grand total = 25. Return again to id =1 and check id by id if it is equal to 9, in the case of id not being = 9 fill in with any REGION left to be distributed.

I believe that this way we will no longer have the "it doesn't matter"😊

[lbendlin]

In the case of ANALIZA that has two REGIONS, find the MS region and fill it in, then find the BA region and fill it in.

what makes me choose the MS region first for ANALIZA? Why not the BA region?  Is there some sort of preference?  If so, where is it listed?

[Spotto]

@lbendlin 
i just followed the order by id and region in the example table below and therefore MS came first, it doesn't have to be a fixed rule

[Daryl-Lynch-Bzy]

Hi @Spotto - I have add a Power Query model in Excel to allocate the Base table items to the Analista.  I have uploaded the file below.  You will find a table on the "Assigned Items" contain the results you need.  Please note I added "Test User" to the Analista table to check that the Overflow worked. 

I have achieve this by using several queries rather than building one query or nested functions.  This will help you see the before and after impact of each step, and I have added tests along the way to monitor the work.  This can be combined at later stage into a single query or function.

 Here is a summary of the basic flow:

1. Add "Index" to BASE table
2. Max Rows - this determines the equal number of items that should be allocated to each Analista.
3. Inital Allocation and Overflow - the concept here is to allocate the PDDMCT for the Regoins to the Analista up to the Max Rows, and any items above the max rows are Overflow.  This included the following steps:
   1. assign the Analista to the Base table.
   2. Group By Analista to create temp table
   3. Use the FirstN and Skip function to split the temp table between Allocated and Overflow
   4. Add some Test Columns to monitor the row counts in the Allocated & Unallocated tables
4. Create the "Allocated" query by extracting the Allocated tables from previous Query.
5. Create the "Overflow" query by extracting the Overflow tables from previous Query.
6. Overflow Allocation - this start by identify the Analista who have not reached the Max Rows to allocate the Overflow based on the number of rows remaining for each Analista.  Again the uses the Table.FirstN and Table.Skip. 
7. Items Assigned - the combines the Overflow Allocation and Allocated query.  This is the result
8. Duplication Check - a query to check that an Item was not allocated to 2 people.

As @lbendlin suggests - this model does not adjust for multiple Analista in a region.   This would require another approach to remove duplication at the Allocation step.

@BA_Pete 

[Spotto]

@Daryl-Lynch-Bzy 

Thank you very much for your help, but there are some details that I would like to verify with you (I have attached the file that I tested below):
1) I did a first test on a smaller base with 29 lines and I noticed that the total of the 29 lines are not being distributed to all names (2 lines were missing to distribute in the attached example)

Example:
I have 29 lines and 4 analysts, which gives the result of 7 per analyst, however
There are 6 regions for the "Test Name" ANALYST only for MA, so you should get other regions to complete the total being:
ALISSON - 7 regions
ANAIZA - 7 regions
ANNELYZE - 7 regions
Test Name - 8 regions
It's missing 2 regions for the "Test Name" and I couldn't find out why.

2) Another point is that we can have two or more analysts for the same region and as you commented there is no rule, I did a test and the result was with duplicate lines.

thanks again for helping me

spottoAnalistaData(1).xlsx 

[Daryl-Lynch-Bzy]

Hi @Spotto - I see that I have overlooked the need to remove nulls in the Overflow query to deal with the scenario.

Please try adding a step to exclude the Nulls before adding the Index in the final step.

[Spotto]

@Daryl-Lynch-Bzy I removed nulls as I said but the total number of lines distributed was missing 1 line.
I have 29 lines that were distributed 28.
Your solution was very nice, but I have this doubt and if I have two ANALYSTS for the same region, this is duplicating the lines and the PDDMCT.

[Spotto]

does anyone have any suggestions?

[BA_Pete]

Hi @Spotto ,

The link to the example file you provided isn't publicly shared, so can't access it.

Can you share it publicly, or crete the tables using Enter Data in Power Query and paste the M code here please?

Thanks,

Pete

[Spotto]

@BA_Pete I fixed the link, tks for help

[BA_Pete]

Hi @Spotto ,

This is a VERY tricky one. I'm going to need to think about it over the weekend.

Hopefully can get you something early next week.

Pete

[Spotto]

thank you so much for spending your time helping me

[BA_Pete]

Hey @Spotto ,

Just a quick note to say I've not forgotten about you. I've been talking with another Super User ( @Daryl-Lynch-Bzy ) about this behind the scenes, and he says he's got a working example to share with you.

Watch this space 🙂

Pete

[Spotto]

 tks @BA_Pete i will wait 

[lbendlin]

Since no two analysts share a region you can remove the analyst from the equation. Now it is only a question of balancing requests by region.

let
    //process region
    FillSlots = (tbl, RegIdx)=> let
        //buffer the table
        buf = Table.Buffer(tbl),
        // save away already assigned entries
        prc = Table.SelectRows(buf,each [Assigned]<> null),
        // items that need processing. current region plus leftovers from prior regions
        src=Table.SelectRows(buf,each [Assigned] = null and [RegionIndex] <= RegIdx),
        // defer (regions that will be assigned later)
        def = Table.SelectRows(buf,each [RegionIndex] > RegIdx),
        // sort by Regionindex desc
        srt = Table.Sort(src,{{"RegionIndex", Order.Descending}}),
         // add assignment index
        idx = Table.AddIndexColumn(srt, "Index", 0, 1, Int64.Type),
        // assign the final region
        rep = Table.ReplaceValue(idx,null,each if [Index]<RowsPerGroup then idx{0}[REGIONAL] else null,Replacer.ReplaceValue,{"Assigned"}),
        // drop assignment index
        res = Table.RemoveColumns(rep,{"Index"})       
        in  
        //concatenate result with saved portions
        prc & def & res ,
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\spottoAnalistaData.xlsx"), null, true),
    BASE_Table = Source{[Item="BASE",Kind="Table"]}[Data],
    #"Grouped Rows" = Table.Group(BASE_Table, {"REGIONAL"}, {{"Rows", each _, type table [PDDMCT=text, REGIONAL=text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    RowsPerGroup = Number.RoundUp(Table.RowCount(BASE_Table)/Table.RowCount(#"Grouped Rows")),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "RegionIndex", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index", "Rows", {"PDDMCT"}, {"PDDMCT"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Rows",{"REGIONAL", "PDDMCT", "RegionIndex"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Assigned", each null),
    #"Added Custom" = List.Accumulate({1..Table.RowCount(#"Grouped Rows")},#"Added Custom1",(state,current)=> FillSlots(state,current))
in
    #"Added Custom"

Here is the result:

[Spotto]

@lbendlin thanks for the help, but I need to separate the regions for analysts and sometimes we will have two or more analysts for the same region.