Forum Discussion
Average Times when there is blank / no data
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
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.
1 Reply
- v-eachen-msft
Community Support
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.