Average for a new column is not working as expected

[Alessandro-laba]

Hi everyone, 

I am having some problems calculating the average of a column I created. 

In my dashboard I have a 'time' column and i want to calculate the average of this. What i have done so far was to create a new column and basically transform the time measure in an integer as below: 

This one as i said is a new column that i created and it displays as below: 

The sum of 'Avg time on page in seconds' is 11.214. The count is: 224 hence when i try to calculate the average i get the result of 50.06. Unfortunately this is wrong. What i would need is 136.28.

To run another test I exported the values and past them into an excel sheet that then i imported it in Bi. At this point when i calculate the average the number i get is actually 136.28 as you can see below: 

What i can't get is why this is happening? Would somebody able to clarify this to me and suggest me a way to fix this?

Thanks in advance,

Alessandro

[v-lionel-msft · Accepted solution]

Hi @Alessandro-laba ,

Please do like this.

1. Add [Hour], [Minute] and [Second] columns in 'Edit Query'(The [Time] column should be 'Time' type.).

2. Calculate the seconds of each time.

// This is a measure
Average of time = 
MAX([Hour]) * 3600 + MAX([Minute]) * 60 + MAX([Second]) 

// This is a calculated column.
Aver of time = 
[Hour]*3600 + [Minute]*60 + [Second] 

Best regards,
Lionel Chen

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