ABC analysis for warehouse pickmovements

[MondioRo]

Hello Everyone,

I am working in a warehousing environment and we would like to optimize locations for fast moving products (place them closer to the packing stations).

To map these fast moving products I would like to set-up a dynamic ABC analysis based on the number of pickmovements.

So far I have found the table in the database that represents the history of all lines that are picked, so each line is one pickmovement for the products that is on the line at that particular date.

I would like to create a visualization that shows me an ABC classification of the products based on the number of pickmovents over a specific period.

In the dashboard I would like to add a time slider and a filter for the customers, so I can change the time period and the customer(s) and the visual shwos me the ABC classification for that customer's products over the chosen period.

On google I've found a couple of tutorials, mostly about sales, but I'm not able to understand what is happening in all the code and therefore not able to reproduce it for this scenario.

I have already created a visual that shows me the products and the number of movements over the selected period and for the selected customer, so I can already see which products are moving a lot, however they are not classified and it still takes a lot of time to manually calculate which products represent X percentage and are classified as A/B/C.

To give a heads up on my knowledge level, I've been on PowerBi for two weeks, I know all the basics (importing data, relations, organizing data, basic visuals). 

Thank you for the support

Some screenshots & the link to the file:

https://ufile.io/kwunvbe5 

[v-mengzhu-msft]

Hi @MondioRo ,

I was unable to open the file you shared, so I had to create some simple data myself for your reference.

Not sure if this is what you want:

I categorize the conditions by creating them using the if function.

Group = IF(SELECTEDVALUE('Table'[moving value])>10,"A",IF(SELECTEDVALUE('Table'[moving value])>5,"B",IF(SELECTEDVALUE('Table'[moving value])<5,"C")))

If it can meet your needs, you can view my pbix file.

If  I misunderstand your meanings, tell me in time, thanks in advance~

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MondioRo]

Hi @v-mengzhu-msft ,

Thank you for this. It is not yet the solution but it is in the right direction.

The datatable I have has one row for each movement towards a location to pick the product on that row. One product can be picked multiple times a day. So I first need a way to take the sum of the movements of one product over the period defined by the slider (i.e. one week, month, quarter). This number will represent the "moving value" in your data example. 

Then when zthese values are known for the period, I would like an ABC distribution based on percentage. So let's say the total number of movements (moving values) is 1000, I want to define the products that make up the upper 20% of the movements as A, the 30% after as B, and the final 50% as C.

I.e. in the screenshot below Product 5972 has had 9 movements on January 3rd, 6 movements on january 4th and 3 movements on the 5th.

If I would translate to excel (which I am more familiar with), I would make a pivot table and then filter on the period to change the pivot table. Then I would manually do the ABC based on the pivot table. Maybe this helps also to explain a little better.

Thank you for your support so far.

Kind regards,

Roland

[FRein]

Hey Roland,

could you solve your issue on this?

I've got exactly the same task at the moments, pick optimization and every line is one pick.


Best,
Florian