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
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%
Resident RockstarSo 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