My DAX/Power BI Knowledge is beginner to intermediate.
I’ve completed 4 modules already on this great site but this model has myself a little confused.
Can anyone help in pointing myself in the right direction please.
My company has many Depots and Bakeries, each site takes delivery of products which are receipted throughout the day in SAP (BIS1)
The same SAP System also books the picked products to the van later (TRAN) on the following morning (Sales Date)
To protect company data,
I have renamed the products to 1 to 23 etc.
Warehouse Operatives renamed from 1 to however many there is in the data set.
Each Warehouse Operative will and can receipt many deliveries per shift into SAP.
The same deliveries will also have 1 or many products onboard to Receipt, different amounts for each product and some products may arrive over 1 or more deliveries by same or different operative.
Our operation is a 24 hour window.
I have also stripped the dummy demo data set to 2 sites only. (Google Drive Link to the Excel Data Set)
All product lines are then picked/loaded to each Van for delivery. (Usually following morning - Sales Date)
In essence in an ideal world - Blue Sky Thinking
The total Sum of units that are receipted into the Warehouse should balance with what is loaded out to total Sum in units to all the vans of the same site.
My goal and desired KPI metrics are:
Establish how many units were receipted into the Warehouse for the next days Sales Date.
Total number of units for each individual product.
How many units in total are then loaded to the vans for each product.
Does it match (Balance off) for each product? - Visual for Dashboard
Where the products do not balance off (Difference in Units = Loss)
I would like to count the Warehouse Operative ID who receipted that product into the Warehouse, is there a pattern or trend? - Struggling to get the relationship right on this one for the data to flow back correctly, most likely because I’m using 2 data table? I’m not sure?
Process Fails that i wish to capture from the SAP Data
However some operatives take shortcuts, fail to check in correctly (Count all the product in, check that all the Stacks, Baskets and units are actually there)
This can then create major differences of what we are actually charged for (Receipts) V’s what we load out to the vans.
A few units here and there is acceptable as units are sometimes missing from a basket (Acceptable/Also damage products)
Please find attached screenshots,
Power BI Desktop File and Excel Data Forum Dataset.pbix (165.9 KB) set