Forum Discussion
Overstock Bracket Value
Hi,
I wonder someone can help.
I am working on Inventory Health to acertain overstocks.
I worked out my stock weeks cover by simply looking at my balance on hand devided by average week sales. I than created a new column to ascertain the following 'overstock brackets':
- 12-16 weeks
- 16-26 weeks
- 26-56 weeks
- 56+ weeks
- No Demand
I now need to ascertain what porpotion of balance on hand quantity is associated to each of the above bracket.
I am mindful that one bracket can overlap other bracket; meaning I can have portion of balance on hand in bracket 16-26 weeks and 26-56 weeks.
Can anyone help how to work this out in PowerBI?
Thank you.
Tomas
5 Replies
Anonymous
I believe that you can use the groupby function to achieve this. If you could provide dummy or sample data to workout the solution it will be very usefulthanks for your feedback.
Please see below sample data for all overstock scenarios.
It's worth noting that 'no demand' bracket is generated if ProductID have no sales.
ProductID Price/Unit (£) Balance on Hand Avg Wk Sale (Units) Avg Wk Sale (£) Wks Cover Overstock Bracket 12 6.71 2257 178 1194.38 12.7 12-16 33 5.64 392 19 107.16 20.6 16-26 67 20.8 989 1 20.8 989 56+ 116 27.22 1557 1412 38434.64 1.1 133 92.43 41 0 0 1000000 No Demand 189 0.39 4518 171 66.69 26.4 26-56 Anonymous
Please include the expected results as well as columnPlease see below results. - it's driven by basic if statement to identify stock on hand that is associated to each overtock criteria.
ProductID Price/Unit (£) Balance on Hand Avg Wk Sale (Units) Avg Wk Sale (£) Wks Cover Overstock Bracket 12-16 16-26 26+56 56+ No Demand 12 6.71 2257 178 1194.38 12.7 12-16 121 0 0 0 0 33 5.64 392 19 107.16 20.6 16-26 164 0 0 0 0 67 20.8 989 1 20.8 989 56+ 14 26 4 933 0 116 27.22 1557 1412 38434.64 1.1 0 0 0 0 0 133 92.43 41 0 0 1000000 No Demand 0 0 0 0 41 189 0.39 4518 171 66.69 26.4 26-56 2394 72 0 0 0
Anonymous
Please show your logic with reasonable sample data to get the expected "121","164", etc.Read this post to describe your sample:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Paul Zheng _ Community Support Team
