Forum Discussion
Overall Fill Rate measure help
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
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.
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%
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