Average In Stock % Matrix

[Anonymous]

Hello,

I am trying to build a matrix showing an average in stock % that drills down by 'Warehouse Master'[Warehouse ID], 'Vendor Master'[Vendor Name], and 'Item Master'[Item Number & Description].

The average needs to iterate through each day on record and calculate the in stock % by doing the sum of 'Inventory Snapshot'[Qty] (from the previous day) (built key measure Historical On Hand for this) and dividing that by sum of 'Order Detail'[Qty Ordered] (built key measure Total Store Qty Ordered for this). 

Additional note: If the in stock % on a given day is greater than 100% then it should return 100%, meaning that 100% is the hard ceiling.

Any help is appreciated.

https://1drv.ms/u/s!AiFttrDOn_FqgRS6FfaNLXA2zmLZ?e=jngT15

[v-xuding-msft]

Hi @Anonymous ，

By my test , your measure is worked. I changed the relationship between table “DateTable” and table “Inventory Adjustment” from single to both. Then the measure of “Historical On Hand” and “Avg In Stock% ” are worked. If the values are not what you want, please post the expected output.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Xue Ding, @v-xuding-msft 

The problem with this solution is that this appears to just be adding up the Historical On Hand and dividing it by the Total Store Qty Ordered (image below). I need the Avg In-Stock % to iterate through each date and do Historical On Hand / Total Store Qty Ordered by item and then be able to roll it up to the vendor and then warehouse level. From a visual standpoint all I need is a matrix that has Warehouse ID/Vendor Name/Item Number & Description and then the corresponding avg in stock %. Also note that the vendor level logic is broken (image below).

Warehouse LevelVendor Level