Creating suggested restock formula for each store that will not exceed current stock in warehouse

[Napon]

Hello everyone. Just started using dax in power pivot and I have a problem I can't figure out what functions I am supposed to use.

I am writing a report that suggests what products should be restocked for each shop. 

1. The ‘suggested restock’ is a measure based on the product’s sales, store’s stock quantity.
2. The products will be restocked from the main warehouse.
3. Therefore total ‘suggest restock’ cannot exceed current inventory in warehouse. And ‘suggested restock’ to stores by prioritizing stores with the lowest stock to sales ratio first. This is the part I can't do.

So this is what I have right now. measure for suggest restock. However its total exceeds the stock in warehouse. Because the measure right now only takes into account the average sales and In Stock quantity.

What I need is for the suggested restock formula to "know" when its total is teh sames as 'stock in warehouse' and 

From what I tried looking around, I think I'm supposed to use a function that creates a new table for each product SKU with the suggest restock for each store, ranked by sales ratio, and use EARLIER to limit the suggest restock level at the current warehouse qty level. 

Any help wil be greatly appreciated. Thank you.

[v-yulgu-msft]

Hi @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

[Napon]

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