Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average Handling Time without 0's

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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" 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous , try this measure:

    Handle Time Average (Excl zero) =
   FORMAT(
      CALCULATE(
         AVERAGE('8x8'[Handle Time Minutes]),
         '8x8'[Handle Time Minutes] <> 0 
       ),
       "HH:MM:SS"
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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