Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating difference between two time values column and finding its average

 

 

Hi Everyone,

 

I need your help to solve a problem that I am facing.

I have two columns which have time based value as shown in the picture:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I found their difference using the formula    Duration.ToText([Reached Merchant]-[Assigned At])), I got the result as follows:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now in order to find average of "Avg store reach time", I made a new measure as follows:

 

Measure = FORMAT(AVERAGE(Sheet1[Avg store reach time]),"HH:MM:SS")
 
 
 
 
 
 
 
 
 
 
 
 
 
Now instead of getting an average of the column "Avg store reach time" I am getting the below error:
 
 
Please help me with this. Thank you
RK
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    try a measure

     

    Measure = format(calculate(averagex('Table',TIMEVALUE('Table'[Avg store reach time]))),"HH:MM:SS")

     

    or

     

    Measure = format(averagex('Table',TIMEVALUE('Table'[Avg store reach time])),"HH:MM:SS")

     

    depends on business logic

8 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try a measure

     

    Measure = format(calculate(averagex('Table',TIMEVALUE('Table'[Avg store reach time]))),"HH:MM:SS")

     

    or

     

    Measure = format(averagex('Table',TIMEVALUE('Table'[Avg store reach time])),"HH:MM:SS")

     

    depends on business logic

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi az38 

       

      Thank you for your help. It worked for me.

       

      Is it ok if I can contact you for any future help?

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        anytime. new threads, private messages, Linkedin.

        the same about the Community - there are a lot of great users passionate to help you

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi Anonymous ,

     

    "Avg Store reach time" is of STRING datatype because your calculation as below returns a text type of value:

    Duration.ToText([Reached Merchant]-[Assigned At]))

     

    Thanks,

    Pragati