Average Handling Time without 0's

[Anonymous]

I need an average handling time. So I have done a new column with the total-time column divide 86400 and put the format as time.

Handle Time Minutes = DIVIDE('8x8'[total-time],86400)

I then created a new measure to figure out the average but it is including 0’s. How can I filter this to not include when the total time is 0 or the Handle Time Minutes is 0.

Handle Time Average = FORMAT(AVERAGE('8x8'[Handle Time Minutes]),"HH:MM:SS")

Is this the best way to do this? As if I put the average time in a matrix then it won't let me conditional format it if it is under KPI.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try this formula.

Handle Time Average =
FORMAT (
    AVERAGEX (
        FILTER ( ALLSELECTED ( '8x8' ), [Handle Time Minutes] <> 0 ),
        '8x8'[Handle Time Minutes]
    ),
    "HH:MM:SS"
)

Best Regards,

Jay 

[Anonymous]

@Anonymous , try this measure:

Handle Time Average (Excl zero) =
   FORMAT(
      CALCULATE(
         AVERAGE('8x8'[Handle Time Minutes]),
         '8x8'[Handle Time Minutes] <> 0 
       ),
       "HH:MM:SS"
    )

[amitchandak]

@Anonymous , the Best is to do Avg and then format, what you have done, unless you have some issue

Handle Time Average = FORMAT(AVERAGE('8x8'[Handle Time Minutes]),"HH:MM:SS")

in case the format does not work you can manually create

var _1 = AVERAGE('8x8'[Handle Time Minutes])

return

Quotient(mod([_1],86400),60) &":" & mod([Total duration ],60) &":00"