Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 TicketsAvgCu Avg90% Fill Rate
0433%33%0
118%42%0
2325%67%0
318%75%0
418%83%0
500%83%5.8
618%92%0
700%92%0
800%92%0
918%100%0

 

Thanks & Regards,

CS

 

Need Help

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

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

       

      Thanks,

      Mihnea

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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 ?