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,
Could somebody help me in getting the performance fillrate in days at 90%. I have the input data as shown below, and would like to know what is the performance in days to resolve the 90% of tickets.
Tickets | Days to resolve |
A | 0 |
B | 2 |
C | 3 |
D | 0 |
G | 2 |
F | 0 |
H | 0 |
I | 4 |
J | 6 |
K | 9 |
L | 2 |
M | 1 |
We have excel calculation which results out the performance in days as shown below. 5.8 Days is the output in the given sample.
Days | # of Tickets | Avg | **bleep** 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,
CS
Solved! Go to Solution.
Hi , @Anonymous
Create mesure as below:
Test =
var currentOrder = MAX('Sheet1'[order_to_delv])
var nextOrder = CALCULATE(MIN('Sheet1'[order_to_delv]),FILTER(ALLSELECTED(Sheet1),'Sheet1'[order_to_delv]>currentOrder))
var NextRunningTotal =
SUMX (
CALCULATETABLE(DISTINCT('Sheet1'[order_to_delv]),
FILTER (
ALLSELECTED ( Sheet1),
Sheet1[order_to_delv] <=nextOrder
)),
CALCULATE(
DIVIDE (
CALCULATE(COUNT ( Sheet1[order_no] )),
CALCULATE (
COUNT ( Sheet1[order_no] ),
ALLEXCEPT ( Sheet1, Sheet1[hp_receive_date], Sheet1[order_no] )
)
)*100)
)
var CurrentRunningTotal = [RunningTotal]
return IF(CurrentRunningTotal<90 && NextRunningTotal>=90,(currentOrder + (90-CurrentRunningTotal)/(NextRunningTotal-CurrentRunningTotal)),0)
Check below pbxi file.
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.
Hi , @Anonymous
Could you please tell me whether your problem has been solved?
If it is, please mark the helpful replies or add your reply as Answered to close this thread?
Best Regards,
Community Support Team _ Eason
Could you kindly provide the same DAX in new measures but not as new column.
Thanks,
CS
Hi , @Anonymous
Create mesure as below:
Test =
var currentOrder = MAX('Sheet1'[order_to_delv])
var nextOrder = CALCULATE(MIN('Sheet1'[order_to_delv]),FILTER(ALLSELECTED(Sheet1),'Sheet1'[order_to_delv]>currentOrder))
var NextRunningTotal =
SUMX (
CALCULATETABLE(DISTINCT('Sheet1'[order_to_delv]),
FILTER (
ALLSELECTED ( Sheet1),
Sheet1[order_to_delv] <=nextOrder
)),
CALCULATE(
DIVIDE (
CALCULATE(COUNT ( Sheet1[order_no] )),
CALCULATE (
COUNT ( Sheet1[order_no] ),
ALLEXCEPT ( Sheet1, Sheet1[hp_receive_date], Sheet1[order_no] )
)
)*100)
)
var CurrentRunningTotal = [RunningTotal]
return IF(CurrentRunningTotal<90 && NextRunningTotal>=90,(currentOrder + (90-CurrentRunningTotal)/(NextRunningTotal-CurrentRunningTotal)),0)
Check below pbxi file.
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.
Thanks, would you help to get total value of "test" measure when i put on visualization. As now, it returns 0.
Regards,CS
Thank you very much!
The variable for "NextRunningTotal" is actually doing what it meant to be, but even on the first row. The first value should remain same, and for the next ones it should be as nextrunningtotal.
Could you please help with this.
https://drive.google.com/open?id=1GiqLrk9mCVh8kQ6Tg-XpDceHne1OT61W
Best Regards,
CS
Hi , @Anonymous
Not very clear the variable for "NextRunningTotal" . Is "NextRunningTotal" another measure?
Please explain more about it .
It will be better if you can share my the expected result in excel.
Best Regards,
Community Support Team _ Eason
Thanks, please find the attached excel.
https://drive.google.com/open?id=1_9wCC1qBk1P8UKeqt57OhGJ4I9rcXYLQ
The NextRunningTotal is not a new measure, am sorry if have confused you. May be the excel attached might give you clear understanding.
Regards,
CS
Hi, @Anonymous
Could you please tell me whether your problem has been solved?
If not , please tell me more details.
If it is, you can add your reply as Answered to close this thread.
Best Regards,
Community Support Team _ Eason
Hello,
Please find the attached excel with data set and required output.
My input will be column A,B,C from input tab.
And output measure should be Fillrate value.
https://drive.google.com/open?id=1k1wAKwGBZI-JSHr6jGbR7LfCv4n77svl
Thanks,
CS
Hi, @Anonymous
Your table makes me even more confused.😔
Can you show me the result of the ”Next running total "you want, I can't get anything new from your excel.
Best Regards,
Community Support Team _ Eason
Hi , @Anonymous
Try measures as below :
Column =
IF (
'Table 2'[**bleep** Avg] < 0.9
&& CALCULATE (
SUM ( 'Table 2'[**bleep** Avg] ),
FILTER ( 'Table 2', 'Table 2'[Days] = EARLIER ( 'Table 2'[Days] ) + 1 )
) >= 0.9,
[Days]
+ ( 0.9 - [**bleep** Avg] )
/ (
CALCULATE (
SUM ( 'Table 2'[**bleep** Avg] ),
FILTER ( 'Table 2', 'Table 2'[Days] = EARLIER ( 'Table 2'[Days] ) + 1 )
) - [**bleep** Avg]
),
0
)
Here is a demo.
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 ,
Few which I can get looking at data. please provide calc logic
Ticket = sum(table[# of Tickets])
Total Ticket = calculate(sum(table[# of Tickets]),all(Table))
Running ticket = calculate(sum(table[# of Tickets]),filter(all(Table),table[Days]<=max(Table[Days])))
Avg =divide([Ticket],[Total Ticket])
**bleep** Avg =divide([Running ticket],[Total Ticket])
Appreciate your Kudos.
Hi,
Formula used for fillrate
=IF(AND(I2<0.9,I3>=0.9),($F2+(0.9-I2)/(I3-I2)),0)
Where Column I is **bleep** AVG and F is Days.
Should i create a new table and bring in the # of tickets according to day and then proceed with the calculation measure?
Regards,
CS
Can you explain these calculations?
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |