Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi @wcameron14 ,
You can try the following measure
Display =
This still displays a decimal on the card. Maybe there's something I'm doing wrong with the formatting of my visual?
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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!
Welcome, kudos (thumps up are always welcome)
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
@stevedep I went back and added pictures to the post to make it a little easier to understand.
Thanks, that's helpful. How is the session duration stored in your dataset? Is it a duration in seconds or minutes (sum of)?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
It is stored as a duration in the dataset. D.hh:mm:ss.0 (See below).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |