Forum Discussion
A seemingly complex task...
Hey all,
I've got what might be a fun one for all you experts. Here's what I'm trying to do:
If any of you have a background in warehouse management, you might get this right off the bat. I'm essentially trying to come up with a way to automate a pick sequence. What I want to do, is filter my inventory table to display the pallets that need to be picked, in order, until the ordered quantity is met.
In the following picture, you can see my example. So, table on the left is from my orders table. It has product and quantity ordered. The table on the right is inventory. When I select the product on the left, I want the table on the right to return all the records up until that black arrow. ( It just happens to be the total of ordered quantity, or 59,400. ) Now, if the ordered quantity were 60,000, I'd want the table to return the next row, even though the total would be 60,840. So, it has to be filtered to AT LEAST the total for ordered quantity, or, all records that exist if we don't have more than ordered. Now, the kicker is this has to be picked in sequence, and we pick by pallet. So, I think whatever happens, I need to be able to rank pallet id by Lot_no first, which is sequential, and then by FIFO_date. This would essentially create a pick sequence by pallet number for each product ordered. ( Note, Inventory and Orders table are connected via 3rd dimTable on Item_no )
I hope I explained this correctly. If not, I can try to build a sample *.pbix with fake data as I'm using sensitive information. Thanks for all the help! =D
18 Replies
What you are describing is quite doable...but you are right, its complex. Would you like to do a video session and look over the steps you need to take ? It would be much easier that going back and forth in email.
Email me [email protected] with a good time to talk and I'll send you a Zoom invitation.
Power BI is easy to learn, but it has hard parts.
I'm a personal Power Bi Trainer I learn something every time I answer a questionSo
If your orders were in a relationship to your inventory.
And you created a slicer for your orders.
When you selected an order in the slicer.
It would filter your inventory.
If you had your inventory table sorted by pallet id by Lot_no first, which is sequential, and then by FIFO_date, then you could create a "running total" for each product, and you would be able to see how many pallets you needed to pick to hit the number needed by the order.
Then the question is, how do you want to transmit/print the pick list to the warehouse ?Yes, we also need to create a running total measure as well, so that we can compare to ordered quantity.
And yes, I have a relationship between orders and inventory via an item_no master table.
That would be extremely helpful, however, I'm limited in sharing data since it's all confidential information.
Do you mind if we just go step by step here?
I think the first thing we need to do is create a measure for Pallet Pick Sequence, which would be a RANKX measure of Pallet_ID, ranked first by minimum value in LOT_NO, then minimum value in FIFO_DATE. Correct?
I don't know if you need a ranking... i think just sorting the table would give you want you want. What you are going to need is some sort of index field to use in doing the running total. It's possible you could use the ranking as an index to step to the "next" pallet when you are doing the running total.
After thinking about this for while, it seems like a problem that should be solved in part in the transaction system, rather than in power bi. I've built picklist systems, when the order systems needs to generate a picklist there should be a set of records that attach to the order that list the pallets that were "picked"
After the pick it sounds like the number of items on some pallets will need to be adjusted and that probably should be done in the transactional database as well. Because the next order for the same item will need to know that the previously picked pallets are no longer available.I realize you might not have that kind of choices about which systems does which actions... but it's an interesting question to think about,
We're running a pretty advanced WMS with a lot of a features.
What this report will be used for is to generate a list of product for a planned inventory move from overstock locations to foward pick locations. Essentially, they want to move enough product from overstock locations to floor locations, the night before they start picking the orders, so that the pickers aren't held up by replenishments and don't have to pick from overstock.
