Forum Discussion

Dolf's avatar
Dolf
Regular Visitor
2 years ago
Solved

How to get average time spend on card

I have data from a Service Desk system and building a dashboard for management and need average time. 

i have created this mesure to convert the time to "hh:mm:ss"

First Response Time in Time =
TIME(
    DIVIDE(
        [First Response Time in Bhrs] , 3600000
    ),
    DIVIDE(
        MOD(Total_tickets_data[First Response Time in Bhrs] , 3600000) , 60000
    ),
    DIVIDE(
        MOD(Total_tickets_data[First Response Time in Bhrs] , 60000) , 1000
    )
)
 
However I am unable to get the average on a callout card and need help please?



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Dolf 

    Based on your information, i created a table:


    Create a new Measure , then use the following DAX:

    Average First Response Time = 
VAR AvgTimeInBhrs = AVERAGE('Table'[First Response time])
RETURN
TIME(
    DIVIDE(AvgTimeInBhrs, 3600000),
    DIVIDE(MOD(AvgTimeInBhrs, 3600000), 60000),
    DIVIDE(MOD(AvgTimeInBhrs, 60000), 1000)
)

     

    Change its time type to hh:mm:ss

     

     

    This measure is then placed in the card view, and when a piece of data is selected, the card will display the corresponding time, the following is my preview:

     

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Dolf 

    Based on your information, i created a table:


    Create a new Measure , then use the following DAX:

    Average First Response Time = 
VAR AvgTimeInBhrs = AVERAGE('Table'[First Response time])
RETURN
TIME(
    DIVIDE(AvgTimeInBhrs, 3600000),
    DIVIDE(MOD(AvgTimeInBhrs, 3600000), 60000),
    DIVIDE(MOD(AvgTimeInBhrs, 60000), 1000)
)

     

    Change its time type to hh:mm:ss

     

     

    This measure is then placed in the card view, and when a piece of data is selected, the card will display the corresponding time, the following is my preview:

     

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • samratpbi's avatar
    samratpbi
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    First you need to have average of all the rows then try to convert into hh:mm:ss. Hence in your measure, create a variable like VAR _avgTime = AVERAGE (column_name). Then try to extract Hour, Min, Sec from there.

    If this resolves your problem then please mark it as solution, Thanks!

  • Dolf's avatar
    Dolf
    Regular Visitor
    2 years ago

    I have created this measure:

    VAR _avgTime = AVERAGE (Total_tickets_data[First Response Time in Bhrs])

    Then used this to convert to time, the problem is your "Card" can not provide an average. How would you get the average per Agent?
  • samratpbi's avatar
    samratpbi
    Icon for Super User rankSuper User
    2 years ago

    Hi, I have created a sample table like below:

    Then created below measure:

    Avg Time Spent =
    VAR _avgT = AVERAGE(Avg_Time[Time])
    VAR _hr = FORMAT(TRUNC(DIVIDE(_avgT,3600)),"00")
    VAR _min = FORMAT(TRUNC(DIVIDE(_avgT - _hr*3600,60)),"00")
    VAR _sec = FORMAT(TRUNC(_avgT - _hr*3600 - _min*60),"00")
    RETURN
    _hr & ":" & _min & ":" & _sec
    And the result is below:

    I can see the Agent wise average.

    If this resolves your problem then please mark this as solution, Thanks!

    • Dolf's avatar
      Dolf
      Regular Visitor
      2 years ago

      Now put that on a "Card", it does not work.