Retail,Warehouse & Ideal Stock

Hi all,

Need assistance regarding this issue I had. Have a user that wants to maintain a list of ideal stock inventory in the retail stores while also looking at the warehouse stock. The idea is that when stock in retail goes below ideal stock quantity, the user can have a quick glance on what is available in the warehouse stock. The granularity of the data would be by material & size.

The issue i have is that the warehouse stock qty is not displaying the correct information.

Actual Warehouse Stock Screenshot

Capture

i tried to use natural inner join thinking it should be similar to T-SQL but it does not work too.

Below is a photo of data model

Below is a copy of the source & pbix files.

Files.zip (189.8 KB)

Regards
Hidayat

Hi
I recommend that you view the courses on data modelling and DAX measures.
Your data model is not right, also you apply DAX measures that can be much simpler. (no need to add CALCULATE to SUM).
I have attached an example with your data. Assuming there is a data in your actual data, add a date table.
Check the query editor, I have grouped your data here.Stock Replenisment PL.pbix (128.6 KB)
Paul

@Paul

Thanks for the grouping! Bad habit of mine to use calculate & sum in the same statement.

I manage to figure out a solution to my issue. The whole idea is to create a unique tool to help users replenish stocks in the retail store when stocks go below the ideal level.

The criteria will be as follows:

  1. Show the list of material in Retail Store.
  2. Show the list of qty for the same material in warehouse.
  3. Map against the ideal stock level.
  4. When Retail Qty is less than ideal qty, calculate the difference.
  5. When there is difference, calculate the replenishment qty required.

In order to make the model dynamic, I would first need to introduce a reference table called size. After which, the Ideal stock, Market Warehouse and Stock retail tables are connected to the size table.

The output result will be a model where users can select specific store to view the list of material and also have it reference the warehouse qty and ideal qty.



Regards
Hidayat

Hi
Great, on this topic check out:


This provides features on restocking level calculations.
Paul

I would review this part of your model here. I don’t recommend this

…and I don’t believe it is required if you set up your model correctly.

For all my best practices ideas check out this particular course module here.

Sam