Duration Displayed on Card

[wcameron14]

Hello,

I am working with Google Analytics data and attempting to display a card with the Average Session Duration. I have created a custom column to display this data on my table in Power Query with the following formula:

= Table.AddColumn(#"Changed Type", "Average Session Duration", each [Session Duration] / [Sessions])

 I know from viewing GA that the outcome I'm looking for is 0:01:17. From working with the data in Excel, I know that I can get the right answer with the following equation: Sum(Session Duration) / Sum(Sessions).

When I go to display this value on a card, it shows as a decimal.

As time. Only Summarization options are "Don't Summarize" and "Count"

I changed the Data Type to "Time" to get to the format but I can only Summarize the data with "Count" or "Don't Summarize", neither of which give me the average that I'm looking for.

As a decimal number

 My calculation may be wrong but I can't tell based on the output that I'm getting. Any thoughts on how to get to the right number and display?

[harshnathani]

Hi @wcameron14 ,

You can try the following measure

Display =

var TSD = CALCULATE(Sum('Table'[Total Session Duration]),ALLSELECTED('Table'[Date]))

Var Sess = CALCULATE(Sum('Table'[Total Session]),ALLSELECTED('Table'[Date]))

RETURN

DIVIDE (TSD,Sess)

Regards,

Harsh Nathani

[wcameron14]

This still displays a decimal on the card. Maybe there's something I'm doing wrong with the formatting of my visual?

[stevedep · Accepted solution]

What you could do is create a calculated column:

averagepersessioncol = 'Table'[Total Session Duration]/'Table'[Sessions]

And a measure:

durationpersession = Format(AVERAGE('Table'[averagepersessioncol]); "HH:MM:SS")

For the 3rd and 4rth row this yields:

Which is the average of averages.. like here in excel:

Link to Power BI file here. 

please mark as solution if this works for you.

Kind regards, Steve. 

[wcameron14]

This helped with the formatting. My formula isn't quite right but I think I can figure it out from there. Thanks for the help!

[stevedep]

Welcome, kudos (thumps up are always welcome)

[stevedep]

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[wcameron14]

@stevedep I went back and added pictures to the post to make it a little easier to understand. 

[stevedep]

Thanks, that's helpful. How is the session duration stored in your dataset? Is it a duration in seconds or minutes (sum of)?

[wcameron14]

It is stored as a duration in the dataset. D.hh:mm:ss.0 (See below).