Calculate 90% fillrate

[Anonymous]

Hello,

I have data table with tickets for multiple issues per dates as shown below. And from which, the TAT is calculated in new column.

In another table, there is index with days from 0-1000.

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

Now would like to calculate in new column - new table

1) Count of tickets against each day mentioned in index

2) Avg of tickets for each day

3) Cummulative avg

4) Finally a formula which works in excel to determine the 90% fillrate

=IF(AND(I2<0.9,I3>=0.9),($F2+(0.9-I2)/(I3-I2)),0)

where "I" is Cu Avg and "F" is days.

table below for ref.

Days	# of Tickets	Avg	Cu Avg	90% Fill Rate
0	4	33%	33%	0
1	1	8%	42%	0
2	3	25%	67%	0
3	1	8%	75%	0
4	1	8%	83%	0
5	0	0%	83%	5.8
6	1	8%	92%	0
7	0	0%	92%	0
8	0	0%	92%	0
9	1	8%	100%	0

Thanks & Regards,

CS

[v-frfei-msft]

Hi @Anonymous ,

Could you please tell me what is the logic of the # of Tickets in your scenario? Based on that, we can create the calculated columns as below to work on it.

Avg_ = DIVIDE('Fillrate_'[# of Tickets],SUM('Fillrate_'[# of Tickets]))

cu avg _ = CALCULATE(SUM('Fillrate_'[Avg_]),FILTER('Fillrate_','Fillrate_'[Days]<=EARLIER('Fillrate_'[Days])))

Fillrate_ = 
IF (
    AND ( 'Fillrate_'[Avg_] < 0.9, 'Fillrate_'[cu avg _] >= 0.9 ),
    DIVIDE (
        'Fillrate_'[Days] + ( 0.9 - 'Fillrate_'[Avg_] ),
        ( 'Fillrate_'[cu avg _] - 'Fillrate_'[Avg_] ),
        0
    )
)

 

For more details, please check the pbix as attached.

[Anonymous]

Hi there,

in this AND condition

AND ( 'Fillrate_'[Avg_] < 0.9, 'Fillrate_'[cu avg _] >= 0.9 )

shouldn't it be AND ('Fillrate_'[cu avg<0.9 ? 

And is it possible to adapt this formula to calculate the 90% fill rate with a condition? For instance assuming there are different categories of tickets ? 

[Anonymous]

Hello,

But what does this fill rate calculate, it isn't the days needed to fulfill 90% of the tickets, right?

Thanks,

Mihnea

[Anonymous]

Hi,

Thanks for your support here!

# of tickets is distinct count of tickets per ageing.

Would you mind showing the samelogic in measures than column.

Regards,

CS