(Yet another) time query

[Anonymous]

Hello all

I've recently linked Power BI to Google Analytics, and my customer wants to focus on Average Time on the Page.

The data is brought into the model in a 'Duration' format, and looks like this (I'll upload a screenshot too) : 0.00:00:03.6666667

Of course there are many different number combinations, but the format is always the same.

I need to convert it to either a TIME format that I can use in mathematical calculations, or preferably convert it to SECONDS and then work out an average from that.

So, firstly, how can I convert the above to SECONDS please (If possible in the Query Editor, as I have tried converting it to TIME in the 'Modelling' tab and any SECOND calculations I run all seem to be a second out.

Secondly, is there a way to run an accurate average on this metric, as of course a mathematical average gives me one answer (for example Average Time on Page = 36 seconds), but when I run a corresponding report on GA it gives me Average Time on Page = 41 seconds.

I hope that makes sense, as I've read what seems like a million posts on here around TIME, but I can't find a solution that works for me.

Any help you can give me is much apreciated.

Kind Regards

Daniel

[vivran22]

@Anonymous 

Hi,

If the column is loading as duration and you need to convert it as total seconds then use following:

Transform or Add Column > Duration (Under Date & Time segment) > Total Seconds

It will convert the existing duration column in seconds.

I am not sure about your second requirement because as per my experience, average calculation in Power BI gives accurate results. I would recommend you to calcualte average after performing the steps above.

Rgds,

Vivek

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

[Anonymous]

Hi vivran

As the screenshot will show, I have explored the Time functions under transforming columns, and I cannot see find a seconds option:

What I did find though was the 'Duration' button lit, and that gives an option to split out seconds from the duration, and, although it doesn't deal with milliseconds, this may move me further forward.

Thanks for taking the time to reply

Daniel

[vivran22]

@Anonymous 

Hi,

Forgot to mention: The option I have suggested is available in Power Query and not in Power BI. You need to open the table in the Edit Query mode to access the feature.

Rgds,

Vivek

[Anonymous]

Thanks Vivran

So the Duration button has given me the seconds I need, however, I'm now trying to replicate the Average Time on Page and Bounce Rate metrics from Google Analytics.

When I put my measures in a table I see the following:

However , the totals are different (in some cases as much as 5%) from those that Google Analytics are reporting.

As you can see from the table, I've tried weighting the average to minimise the difference, however it is still enough to render the table completely innaccurate to it's audience.

Is there a calculation method I'm missing to get this closer to the mark?

Daniel

[vivran22]

Hi,

What is the calculation of average and is it possible to share the sample data and the expected results from google analytics?

Rgds,
Vivek

[Anonymous · Accepted solution]

I think I've solved it myself!

I was asking for daily data, and Power BI was trying to average, but by removing day from my query, it is retuening the expected results.

Thanks for your help!