Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good Day,
I could nee some assistance on Average times from Data when I may not have a time entry
For Example
NUMBER | CALL | DISP | CMPLT |
1 | 13:48:32 | 13:50:44 | 14:09:55 |
2 | 10:01:01 | 10:19:58 |
I would like to show an Average Time from DISP to CMPLT
If I use the following calc measures , ( I know I could bring this all into one , this just allows me to stay organized)
AVG CMPLT = CALCULATE(AVERAGE(rlmain[Time]),FILTER(rlmain, FIND("CMPLT",rlmain[tencode],, BLANK()) <>0))
AVG DISP = CALCULATE(AVERAGE(rlmain[Time]),FILTER(rlmain, FIND("DISP",rlmain[tencode],, BLANK()) <>0))
AVG VALUE_CMPLT-DISP = [AVG CMPLT]-[AVG DISP]
TIME FORMAT_AVG VALUE_CMPLT-DISP = TIME([AVG VALUE_CMPLT-DISP]*24,0,MOD([AVG VALUE_CMPLT-DISP]*24*3600, 3600))
I will use the TIME FORMAT_AVG VALUE_CMPLT-DISP on a card for display
My Average card shows an Error , whereas if I select each entry separately it shows 00:19:11 for the Number 1 event and displays 10:19:58 for the Number 2 Event
I tried another calc/measure
AvgDistoCmplt = format((CALCULATE(AVERAGE(rlmain[Time]),FILTER(rlmain, FIND("DISP",rlmain[tencode],, BLANK()) <>0))/CALCULATE (
DISTINCTCOUNT( rlmain[callid]),
CONTAINSROW (
{ "DISP", "CMPLT"},rlmain[Tencode]
)))-(CALCULATE(AVERAGE(rlmain[Time]),FILTER(rlmain, FIND("CMPLT",rlmain[tencode],, BLANK()) <>0))/CALCULATE (
DISTINCTCOUNT( rlmain[callid]),
CONTAINSROW (
{ "DISP", "CMPLT"},rlmain[Tencode]
))) ,"hh:mm:ss")
I will use the AvgDistoCmplt on a card for display
My Average card shows an 00:47:54 , whereas if I select each entry separately it shows 00:19:11 for the Number 1 event and displays 10:19:58 for the Number 2 Event
How can I work this to ignore the no entry for DISP
Thank You
Solved! Go to Solution.
Hi @bcampbell ,
You could do the subtraction calculation firstly. Then calculate the average with the filter of "DISP<>BLANK() and CMPLT<>BLANK()".
You could ignore the null value with this logic.
Hi @bcampbell ,
You could do the subtraction calculation firstly. Then calculate the average with the filter of "DISP<>BLANK() and CMPLT<>BLANK()".
You could ignore the null value with this logic.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |