cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Average for a new column is not working as expected

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: 

 

ga3.PNG

 

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

 

ga5.PNG

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: 

 

ga4.PNG

 

 

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

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @Alessandro-laba ,

 

Please do like this.

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

v-lionel-msft_0-1606717059044.png

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] 

v-lionel-msft_1-1606717138150.png

 

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.

 

 

View solution in original post

1 REPLY 1
Community Support
Community Support

Hi @Alessandro-laba ,

 

Please do like this.

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

v-lionel-msft_0-1606717059044.png

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] 

v-lionel-msft_1-1606717138150.png

 

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.

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors