Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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 ?
Hello,
But what does this fill rate calculate, it isn't the days needed to fulfill 90% of the tickets, right?
Thanks,
Mihnea
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |