topic Re: Duration Displayed on Card in DAX Commands and Tips

Duration Displayed on Card

wcameron14 — Thu, 04 Jun 2020 13:54:23 GMT

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.

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.

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?

Re: Duration Displayed on Card

stevedep — Thu, 04 Jun 2020 08:58:14 GMT

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

Re: Duration Displayed on Card

wcameron14 — Thu, 04 Jun 2020 13:57:52 GMT

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

Re: Duration Displayed on Card

stevedep — Thu, 04 Jun 2020 14:17:38 GMT

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

Re: Duration Displayed on Card

Anonymous — Thu, 04 Jun 2020 14:27:14 GMT

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

Re: Duration Displayed on Card

wcameron14 — Thu, 04 Jun 2020 15:11:12 GMT

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

Re: Duration Displayed on Card

wcameron14 — Thu, 04 Jun 2020 16:38:50 GMT

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

Re: Duration Displayed on Card

stevedep — Thu, 04 Jun 2020 17:05:14 GMT

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.

Re: Duration Displayed on Card

wcameron14 — Thu, 04 Jun 2020 17:30:40 GMT

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!

Re: Duration Displayed on Card

stevedep — Thu, 04 Jun 2020 17:53:59 GMT

Welcome, kudos (thumps up are always welcome)