Overall Fill Rate measure help

[Anonymous]

Hello,

I am trying to calculate an overall fill rate measure that takes (Total Qty Shipped - Total Qty Credited) / Total Qty Ordered. I want this broken out at the day - warehouse - item level (see below image). My trouble is that the shipped and ordered quantities are coming from Order Detail table while the credited quantities live in the Daily Invoice Details table. I am having a hard time finding a way to relate them so I can use 'Warehouse Master'[Warehouse ID], 'Vendor Master'[Vendor Name], and 'Item Master'[Item ID]. Order Detail is already related to 'Warehouse Master' and 'Item Master'. 

Note: 'Daily Invoice Details'[Vendor Item Number Original] is synonymous with 'Order Detail'[Item Number]

Any help with determining how these relationships need to be adjusted is greatly appreciated. File location is below.

https://1drv.ms/u/s!AiFttrDOn_FqgRZ-6C6TUdmfPWwd?e=ScIDp6

[Cmcmahan]

Wow, this is a massive dataset.

I'm really intrigued by this setup. It seems like it should be 95% of the way to working.  I'm guessing all you need to do is to create a few non-active relationships and use USERELATIONSHIP to specify which relationship to use.

Just to make sure my changes are correct, can you provide the expected results for a few specific Date/WarehouseID/ItemNumber combinations?  

EDIT: quick question: Is [Total Qty Credited] meant to be negative or positive? What does a positive number indicate vs a negative number?

I was able to create a non-active relationship between 'Daily Invoice Detail'[Vendor Item Number Original] and 'Item Master'[Number], and then change the credited measure to:

Total Qty Credited = CALCULATE( SUM('Daily Invoice Details'[Qty Invoiced]), USERELATIONSHIP('Item Master'[Item Number],'Daily Invoice Details'[Vendor Item Number Original]) )

Once I also updated the Overall Fill Rate to use the equation you gave, I got results, I just have no idea if they're correct.

[Anonymous]

@Cmcmahan 

To answer your question the credits should be negative. So really the overall fill rate formula should be:

Total Qty Shipped + (Total Quantity Credited) / Total Quantity Ordered (w/ Total Quantity Credited being a negative number)

Use the below screenshot for the example of what I would expect to see as an output.

'Date Table'[Date]: 6/1/2019

'Warehouse Master'[Warehouse ID]: 1133

'Item Master'[Item Number & Description] & 'Daily Invoice Details'[Vendor Item Number Original]: 10028; 10029

Expected Overall Fill Rate Results: 

10028 = (126 - 2) / 126 = 98.4%

10029 = (110 - 2) / 110 = 98.2%

[Cmcmahan · Accepted solution]

So I've got it working for your example data.  Just be aware that you DO have some positive values for Total Qty Credited in your data.

Other  than those weird values, I have your fill rate working with these measures:

Total Qty Credited = CALCULATE( SUM('Daily Invoice Details'[Qty Invoiced]), USERELATIONSHIP('Item Master'[Item Number],'Daily Invoice Details'[Vendor Item Number Original]) )

Overall Fill Rate = ([Total Qty Shipped]+[Total Qty Credited])/[Total Store Qty Ordered]

I believe the only thing I did was add an inactive relationship between 'Item Master' and 'Daily Invoice Details', but I'm not sure what else I changed (measures/relationships/filters/etc) so I'm just going to link you the .pbix I've been playing with.

My apologies if it is a bit messy:  https://drive.google.com/open?id=19CMz7hKtHmvmpjByj6HPmpfQ0eTJbKqD

[Anonymous]

@Cmcmahan 

This worked! Huge thank you!