Overstock Bracket Value

[tomasv]

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

[V-pazhen-msft]

@tomasv  

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/38490

Paul Zheng _ Community Support Team

[tomasv]

@Fowmy 

thanks 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

[Fowmy]

@tomasv 

Please include the expected results as well as column

[tomasv]

@Fowmy  

Please 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

[Fowmy]

@tomasv 
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 useful