Measure to create fillrate based on dynamic

[Anonymous]

Hello,

Could somebody help solving in getting cumulative avg and find fillrate value.

Am currently stuck where running total showing 100%, and when this is corrected, need new measure which shows fillrate value.

Fillrate formula used in excel as below:

=IF(AND(D3<0.9,D4>=0.9),($A3+(0.9-D3)/(D4-D3)),0)

where D value is derived from "running total" and A is from TAT

TAT	# of Incidents	Avg	Running Total	Fillrate
0	1	0.24%	0.24%	0.00
1	45	10.69%	10.93%	0.00
2	276	65.56%	76.48%	0.00
3	47	11.16%	87.65%	3.31
4	32	7.60%	95.25%	0.00
5	14	3.33%	98.57%	0.00
6	6	1.43%	100.00%	0.00

Please be aware that # of incidents is dynamic to dates in pbix slicer attached.

Thanks,

CS

[V-lianl-msft]

Hi @Anonymous ,

You can refer to this thread for cumulative average:https://community.powerbi.com/t5/Desktop/Dynamic-rolling-average-based-on-slicer-selection/m-p/928540 

If the problem persists,could you please share sample pbix with dummy data?(Please mask any sensitive data before uploading)

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

[amitchandak]

@Anonymous ,

I checked the file But rename at visual level making it difficult to understand.

[Anonymous]

@amitchandak 

Hello,

Please find the new pbix with actual field names on visual.

https://drive.google.com/open?id=1YAIoXwsd7xn9VpG34JPpUVjpf0P_N7Fs

Thanks,

CS

[V-lianl-msft]

Hi @Anonymous ,

Please try the DAX below:

Measure 4 =
DIVIDE (
    COUNT ( Sheet1[order_no] ),
    CALCULATE (
        COUNT ( Sheet1[order_no] ),
        ALLEXCEPT ( Sheet1, Sheet1[hp_receive_date], Sheet1[order_no] )
    )
) * 100

RunningTotal =
SUMX (
    FILTER (
        ALLSELECTED ( Sheet1[order_to_delv] ),
        Sheet1[order_to_delv] <= MAX ( Sheet1[order_to_delv] )
    ),
    'Key Measures SOTAT'[Measure 4]
)

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

[Anonymous]

@V-lianl-msft 

Thank you very much! This worked for me.

Could you help with another part which works in excel with below formula:

Fillrate formula used in excel as below:

=IF(AND(D3<0.9,D4>=0.9),($A3+(0.9-D3)/(D4-D3)),0)

Where D is RunningTotal and A is order_to_delv

Regards,

CS

[V-lianl-msft]

Hi @Anonymous ,

I'm sorry. I tried my best to get the fillrate value through DAX calculation, but it didn't work.

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

[Anonymous]

@v-easonf-msft ,

Would you be able to help here, like you provided DAX in new column for similar request.

Thanks,

CS

[v-easonf-msft]

Hi ， @Anonymous 

Refer to  your this post:

https://community.powerbi.com/t5/Desktop/Create-measure-to-obtain-fillrate-in-days/m-p/977903#M467230

If your problem has been solved, please mark the helpful replies as Answered to close this thread.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@V-lianl-msft  Thank you!

[Greg_Deckler]

So a running total generally done like this:

Running Total Column =
  VAR __Table = FILTER('Table',[TAT] <= EARLIER([TAT]))
RETURN
  SUMX(__Table,[Avg])

Not sure I 100% on understanding what you want though.