Create pallets with series of vaule not exceed the certain sum per pallet by using fewest pallets

[Priyange]

Hi All 

Please excuse me for my layman's expression. 

Order_nr	Article	Hight
Order1	Art1	5
Order1	Art2	1
Order1	Art3	8
Order1	Art4	3
Order1	Art5	2
Order2	Art1	1
Order2	Art2	9
Order2	Art3	8
Order2	Art4	5
Order2	Art5	4
Order2	Art6	5
Order2	Art7	4

I need a column in the above table based on the following criteria:
Per "Order_nr" assing pallet numbers where the total height of the pallet does not exceed 10 and the height of the items are combined to use fewer pallets as possible per order.

Example:

Order_nr	Article	Hight	PaletteNr
Order1	Art1	5	1
Order1	Art2	1	2
Order1	Art3	8	2
Order1	Art4	3	1
Order1	Art5	2	1
Order2	Art1	1	3
Order2	Art2	9	1
Order2	Art3	8	2
Order2	Art4	5	3
Order2	Art5	4	2
Order2	Art6	5	3
Order2	Art7	4	2

Could someone tell me how to achive this in PowerBI?

Thank you.
Regards

Priyange

[Priyange]

Anyone?

[v-janeyg-msft]

Hi, @Priyange 

Sorry, I can't see the calculation logic of PaletteNr, can you explain it?

How to get the results?

Janey

[Priyange]

The calculation logic is:

Bundle "Order_nr" and "Articles" based on the "hight" column. Assign an incremental number for each "PalletNo" group.
Conditions:
1. total value of "hight" per group "PalettNr" should not exceed 10.

2. create as less bundles ("PalettNr") per "Order_nr" as possible. 

Real world scenario:

"Order_Nr" are customer orders and "Articles" are items per order. "Hight" = height of all the boxes per item when boxes are stacked on top of each other. The challenge is to group the boxes of items, where all the boxes of same item stays to gether on singel pallet and total of height per pallet should not exceed 10 amd use lesser number of pallets as possible to save the space in the transport truck.  

[v-janeyg-msft]

Hi, @Priyange 

Sorry, I have no way to write a reasonable physical calculation logic based on your summary, DAX can't implement this kind of reasoning calculus. So your requirements are not suitable to be implemented in powerbi. 

Best Regards,

Community Support Team _Janey

[Russell-PBI]

Hi @Priyange, off the top of my head, I'd expect some sort of looping to be required, e.g., pick the tallest first, then find the next tallest that fits in the remainder and so on until filled, then start a new set.

It may be possible in Power BI and someone much cleverer than me may know how, but, just in case, do you have any other tools available to you to do the logic for this prior to pulling it into Power BI?

[Priyange]

Hi, Actually this is done in warehouse picking logic (for which i don't have access to). But I want to create a simulation in Power Bi to visualize different scenarios based on stack height. I assume there is some kind of magic DAX form I could use.