Group or rank for allocation for optimal utilization

[vissvess]

Hi,

My dataset is as follows

ID       Length

SDF    340

GEW  510

DFA    80

SRD   450

ADV  350

AND SO ON.....

and I have rack dimension length to be 1650.

I need a ranking or grouping mechanism for me to suggest the IDs to be places together in the rack of provided length, so that the resource is utilised in optimal manner.

For instance, if 1,2,3 & 4 are grouped, unutilised length is 270. But if 1,2,4 & 5 is ranked for same group, then the total is 1650 makes it completely utlized. So the ultimate aim is to bring the unutilised closed to zero.

Any workaround for this is hugely appreciated and I would be so thankful.

Thanks in advance.

[Stachu]

if I understand the requirement correctly you need to solve a knapsack problem
https://en.wikipedia.org/wiki/Knapsack_problem

There should be plenty of scripts online for Python/R to solve it, it's also possible with Excel Solver. You could potentially use Python/R within PowerBI, so that may be a way forward.

Unfortunatelly I don't think DAX or M would be able to solve that kind of problem, they were written for different purposes.