Forum Discussion

bcampbell's avatar
bcampbell
Icon for Helper I rankHelper I
6 years ago
Solved

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

NUMBERCALLDISPCMPLT
113:48:3213:50:4414:09:55
210: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's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity 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.