Optimization Rules/help

[Anonymous]

Hi everyone,

I hope to give as much as content/problem I can.

I am tring to get a optimization report for packing department in my company. There are certain rules which needs to be fulfilled to get the best possible way to pack the ordered items into the frames and then into the trucks. Here is an example.

A company have ordered 30 items to be shipped. The total weight of the order is 15200lbs. The breakdown of the orders is as follows.

10 items of 7000 lbs, each 700lbs

12 items of 6000lbs, each 500lbs

2 items of 400lbs, each 200lbs

6 items of 1800lbs, each 300lbs

Trucks are used for shipping to the items to customer's location. Each truck limits a weight of 5000lbs with 4 frames. Each frame can hold upto 5 items. Now, the customer is asking to achive a report where based on the above criteria, optimize a way to pack the items into frames effectively to reduce the trucks cost and much of a manual brain power to set the frames with items.

Hope this makes sense. Please let me know if any questions/suggestions.

[Tad17]

Hey @Anonymous 

While I'm sure Power BI can do this it would be long and complicated. I would like to suggest maybe using Excel Solver instead. It is designed specifically for problems like this and would be incrdibly useful as a template for future calculations. If fact, I have a problem in grad school that was very similar to the one you listed.

Load Solver in Excel: https://support.office.com/en-us/article/load-the-solver-add-in-in-excel-612926fc-d53b-46b4-872c-e24772f078ca

Microsoft examples: https://support.office.com/en-us/article/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040

Ablebits tutorial: https://www.ablebits.com/office-addins-blog/2016/06/22/how-to-use-solver-in-excel-with-examples/

Excel-easy tutorial: https://www.excel-easy.com/data-analysis/solver.html

My dad and I both do a lot of work with various BI softwares and when it comes to optimization of individual instances Solver is still the better tool.

If this helps please kudo.

If this solves your problem please accept it as a solution.

[Anonymous]

I am definitely going to look into the links and see. 

But just to be clear, the example is just an example. There is 100s of customers like the ones with different total number of orders with different sizes and lbs. 

But the rule for truck/frames is the same. 

[Tad17]

I understand. Here is a thread where a super user posted something similar to what I said: https://community.powerbi.com/t5/Power-Query/Need-support-on-Converting-Excel-Solver-to-PowerBI/td-p...

In order to state working it up in Power BI I would need a sample of the data.

[Anonymous]

 Hey, This is the first time I have posted something. I prepared an excel sheet with the sample data. How do I attach the excel sheet? I do not see any icon to attach a file. Please guide.

Thanks

[Tad17]

You can share links to dropbox, google drive, or take screen shots and upload those.