Allocating (grouping) data based on a value

[artosi]

Hello. Hoping someone could help. 

I am currently trying to design something that would automatically allocate data equally or group it based on a total value but struggling to translate it into a workable model in Power BI. 

I have a table of 46k rows. Each row represents property ID and a Difficulty Score that comes with that property. Difficulty score is something that has been assigned to each property. Using this, I need to assign each property to number of officers, but to do it in such a way that distribution is done equally based on difficulty score. 

My table for property looks like this and is called Property_Data :

This table has an Index column, which added in Power Query in order to sort the data correctly, Property ID (unique to each property), Difficulty Score assigned to each proeprty and a Running sum of Difficulty Score (added as calculated column). 

I also have another table that lists each officer (Patch) and Capacity level. Capacity basically represents how much each can take on, 1 being 100% and 0.5 being 50%. Currently there are 18 patches. Table looks like this and is called Patch_Number_Capacity:

Again, i have added an Index column in Power Query in order to be able to sort it appropriatly.

So far I have created a measure to work out how much each patch should have as an average of difficulty score, using a simple sum of all difficulty scores divided by sum of capacity. Measure is called Average Per Patch.
In my head, using this figure, I need a measure, that sums each row of running sum of difficulty within Property table, and once it reaches designed average per Patch, groupes it and then starts calculation again moving onto the next Patch, but also overlays it by Capacity. 

In simple terms, it should be along the lines of:

1. Patch Capacity * Average Per Patch - > this should give me a figure of how much to allocate per patch, either full average or 50% of it.
2. Sum each row of Running Sum of Difficulty Score and once it reaches a desired average per Patch, group it and then start the sum again from next property 
3. Process repeats until all 18 Patches have had all 46k properties allocated (distributed) between them based on capacity and difficulty score. 

I did think using something like EARLIER function but cant seem to translate it into practice. 

I hope this makes sense and someone can help and advice. Thank you in advance. 

[Anonymous]

Hi @artosi 

Can you provide the expected Output you want so that can provide more suggestion for you.

Best Regards!

Yolo Zhu