Forum Discussion
Creating suggested restock formula for each store that will not exceed current stock in warehouse
Microsoft EmployeeHi Napon,
Please post sample data in source tables and the necessary formulas for measures and calculated columns.
The ‘suggested restock’ is a measure based on the product’s sales, store’s stock quantity.
How did you calculate the values of ‘suggested restock’? Please share the formula.
Therefore total ‘suggest restock’ cannot exceed current inventory in warehouse.
Are "Stock in Warehouse" and "Total Suggested Restock" original fields in source table? If not, how to get their values?
For your "Result needed table", could you provide more detailed description? I cannot understand why "suggested restock" and "Total Suggested Restock" are different from that in current pivot table, how to determine their correct values?
Regards,
Yuliana Gu
Thank you Yuliana for replying.
The data model is something like this.
Source data:
FactSale Data records sales qty by: day, channel, product, price sold
FactStock data records stock qty by: day, channel (including both store and warehouse), product.
The measures involved in calculating restock is:
Average Sales Qty 14 Days: =CALCULATE(
sum(factsale[sale qty]),
DATESINPERIOD(DimDate[Date],TODAY(),-14,DAY) )
Sales to Stock Ratio: = Divide([In Stock], [average Sales Qty 14 Days])
In Stock: =CALCULATE (
Sum(FactStock[Stock Qty]),
LASTNONBLANK (DimDate[Date], CALCULATE ( Sum(FactStock[Stock Qty])) ) )
Stock In Warehouse: = CALCULATE( [In Stock Qty],
FILTER(ALL(DimChannel), DimChannel[Store Name] = "Warehouse”) )
Restock Formula: =5+(2*[Average Sales Qty 14 Days])-[In Stock]
Suggested Restock: =CALCULATE(
SUMX(FactStock,[Restock Formula]),
FILTER(FactStock, [Restock Formula]>0))
Total Suggested Restock: =CALCULATE(
SUMX(FactStock, [Suggested Restock]),
All(DimChannel) )
So in my current measure, the suggested restock formula only takes into account sales and the store’s stock. Which means that the Total Suggested Restock may be higher than the stock qty in the warehouse (the stock qty that is used to restock the stores).
But the formula that I need, the Total Suggested Restock must not be greater than Stock In Warehouse (because then we will not be able to restock as suggested, and someone will have to manually decide which stores will actually be restocked).
So for products where Total Suggested Restock is going to be greater than Stock In Warehouse, I need the measure to calculate in sequence the restock for each store, ranked by lowest sales stock ratio first, that if Total Suggested Restock is less than Stock In Warehouse then restock according to Restock Formula, then calculate the same for the next store, up until Total Suggested Restock is equal to Stock In Warehouse, which then suggest restock for all other stores for that product as 0.
I’m not sure but I think I’m supposed to use a table function like CALCULATETABLE or SUMMARIZE and RANKX, to create a table with suggested restock by store, ranked by sales to stock ratio. Then use EARLIER to check whether the current Total Suggested Restock is greater than Stock In Warehouse.
Hope I'm making my issue clearer. I'm not quite sure what function I'm supposed to use so its kind of difficult to explain.
Thank you and appreciate all the help.
Regards,
Napon