Forum Discussion
PowerBI Dax Fill Rate Percentage
I'm trying to get a fill rate (%) formula that calculates the fill rate based on entry Status, and is dynamic (i.e., the calculation will update depending on what filters are selected within PowerBI, and can be used in graphics that break down the data (ex., by site/department/date).
I've tried creating measures that count all requests with a "Fill*" and "Unfill":
mxTotal_Filled_Unfilled = CALCULATE(
COUNT([Request_Status_Description]),FILTER('NRTREQUEST',[Request_Status_Description]="Fill" || [Request_Status_Description]="Fill-Pear" || [Request_Status_Description]="Filled-Apple" ||
[Request_Status_Description]="Unfill")
)
As well as a measure that counts just "Fill*" requests:
mxFilterFilledAll = CALCULATE(
COUNT([Request_Status_Description]),
FILTER('NRTREQUEST',[Request_Status_Description]="Fill" || [Request_Status_Description]="Fill-Apple" || [Request_Status_Description]="Fill-Pear")
)
But am not having much luck when tyring to use them within Divide formulas.
I've also tried this measure with no luck (fxVALUE1 gives a value of 1 to each entry):
mxFillRate =
VAR vxTotalFilled_Unfilled = CALCULATE(SUM([fxVALUE1]),'Table'[Status]<>"Request",'Table'[Request_Status_Description]<>"Pending",'Table'[Status]<>"Cancel")
VAR vxTotalFilled = CALCULATE(SUM([fxVALUE1]),'Table'[Status]<>"Request",'Table'[Status]<>"Pending",'NRTREQUEST'[Status]<>"Cancel",'Table'[Status]<>"Unfill")
RETURN
DIVIDE(vxTotalFilled,vxTotalFilled_Unfilled)
Dataset example:
Fill rate on entire data sample = 63% (EDIT: this is for the Fill* vs Unfill statuses only; all other statuses should be disregarded when calculating fill rate on this dataset)
Fill rate if filtered by date (Feb 17) = 100 %
| ID | Type | Site_Dept | Service | Staff | Status | Start | End | Duration | Submitted |
| 654878 | QRT | ULL-745 | NR-SW | Request | 2023-02-16 19:00 | 2023-02-17 7:00 | 0.50 | 2023-02-14 20:12 | |
| 174556 | QRT | RRE-456 | NR-UCL | Pending | 2023-02-16 19:00 | 2023-02-17 7:00 | 0.50 | 2023-02-13 20:12 | |
| 458765 | QRT | RRW-258 | NR-IPU | Ige, Ayomide | Fill | 2023-02-17 7:00 | 2023-02-17 15:00 | 0.33 | 2023-02-15 20:12 |
| 324568 | QRT | RRE-456 | NR-EONH | Safa, Wambdi | Fill-Pear | 2023-02-17 8:00 | 2023-02-17 20:00 | 0.50 | 2023-02-11 20:12 |
| 763875 | QRT | ULL-986 | NRP-IPT | Bala, Khanh | Fill-Apple | 2023-02-17 23:00 | 2023-02-18 7:00 | 0.33 | 2023-02-17 6:00 |
| 542384 | YPO | ULL-745 | NR-IPK | Unfill | 2023-02-18 9:00 | 2023-02-18 17:00 | 0.33 | 2023-02-14 20:12 | |
| 678743 | QRT | ULL-986 | NR-IPT | Cancel | 2023-02-18 8:00 | 2023-02-18 17:00 | 0.38 | 2023-02-14 20:12 | |
| 320506 | QRT | RRW-258 | NR-EONH | Eluney Noy | Fill | 2023-02-18 19:00 | 2023-02-19 7:00 | 0.50 | 2023-02-14 20:12 |
| 423578 | QRT | ULL-821 | NRP-IPT | July, Onyeka | Fill | 2023-02-19 7:00 | 2023-02-19 19:00 | 0.50 | 2023-02-14 20:12 |
| 687531 | YPO | RRE-456 | NR-IPK | Unfill | 2023-02-19 8:00 | 2023-02-19 16:00 | 0.33 | 2023-02-14 20:12 | |
| 661452 | QRT | RNC-556 | NR-IPT | Unfill | 2023-02-19 23:30 | 2023-02-20 7:30 | 0.33 | 2023-02-14 20:12 |
PBR_, like this?
Rate = VAR _unfill = COUNTX ( FILTER ( data, LEFT( [Status], 6 ) = "Unfill" ), [Status] ) VAR _fill = COUNTX ( FILTER ( data, LEFT( [Status], 4 ) = "Fill" ), [Status] ) RETURN DIVIDE ( _fill, _unfill )Best Regards,
Alexander
4 Replies
- barritown
Solution Sage
Hi PBR_,
You might have provided a wrong value for the fill rate on entire data sample ( 5 / 11 -> 45% ).
If that's the case, please try such a measure:
In plain text:
Rate = VAR _total = COUNT ( data[Status] ) VAR _fill = COUNTX ( FILTER ( data, LEFT( [Status], 4 ) = "Fill" ), [Status] ) RETURN DIVIDE ( _fill, _total )Best Regards,
Alexander
Thanks, Alexander
Sorry - to clarify, the fill rate should only inlcude the Fill* vs Unfill statuses (i.e., the Request, Pending, and Cancel statuses should be removed from the fill rate).
Thanks, Alexander!
I slightly modified it to this and it seems to be working well, including when applying slices/filters.
Rate = VAR _unfill = COUNTX ( FILTER ( data, LEFT( [Status], 6 ) = "Unfill" ), [Status] ) VAR _fill = COUNTX ( FILTER ( data, LEFT( [Status], 4 ) = "Fill" ), [Status] ) RETURN DIVIDE ( _fill, (_unfill+_fill) )Thank you for your help!
