Average of time records

[Anonymous]

Hi,

I want to calculate the time that a certain object was at a point. I have the travel time of the object and I also have the absolute time of the object. I want to know the time at the point of the object for all Absolut time records, this looks like this for example:

my current formula looks like this:

TimeAtPoint = CALCULATE([TotalAbsoluteTime]) - CALCULATE([TotalTravelTime])

Measures used are:

//TotalAbsoluteTime = SUM(Table[AbsoluteTime])

//TotalTravelTime = SUM(Table[TravelTime])

although this works, I know this is'nt correct since the TotalAbsoluteTime is exceeding 24 hours. since it is exceeding 24 hours the next step gives wrong data. The next step is to calculate the average of the TimeAtPoint per object.

Thanks in advance !

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

From the example you provided, you want to get the value of TimeAtPoint for each Object record, right? If so, you can add a calculated column in original table using this simple formula:

TimeAtPoint = 'time records'[AbsoluteTime]-'time records'[TravelTime]

To get the average value of TimeAtPoint per Object, you can still create a calculated column using this formula:

Average =
CALCULATE (
    AVERAGE ( 'time records'[TimeAtPoint] ),
    ALLEXCEPT ( 'time records', 'time records'[Object] )
)

Regards,
Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft,

This worked perfectly ! this was exactly what I was trying to achieve.

Thankyou very much for helping me.

Kind regards,

L. Meijdam