Forum Discussion
Calculate 90% fillrate
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
4 Replies
v-frfei-msftCommunity Support
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.