Forum Discussion

wcameron14's avatar
wcameron14
Frequent Visitor
6 years ago
Solved

Duration Displayed on Card

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?

card
duration
Google Analytics
Power Query
  • stevedep's avatar
    stevedep
    6 years ago

    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. 

9 Replies

    • wcameron14's avatar
      wcameron14
      Frequent Visitor
      6 years ago

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

      • stevedep's avatar
        stevedep
        Icon for Memorable Member rankMemorable Member
        6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      wcameron14
      Frequent Visitor
      6 years ago

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

      • stevedep's avatar
        stevedep
        Icon for Memorable Member rankMemorable Member
        6 years ago

        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.