Forum Discussion

SRK_23's avatar
SRK_23
Helper I
7 years ago
Solved

Calculate Average time

Hi, how to calculate average time by "PlaformEntityName" ? 

 

I've got 3 calculated column which are:

 

 
Duration_Max = CALCULATE( MAX(dm_pageview_company[Date]) , 
                          FILTER( dm_pageview_company , 
                                  dm_pageview_company[SessionId] =  EARLIER(dm_pageview_company[SessionId]) 
                                ) 
                            )
 
 
 
Duration_Min = CALCULATE( MIN(dm_pageview_company[Date]) , 
                          FILTER( dm_pageview_company , 
                                  dm_pageview_company[SessionId] =  EARLIER(dm_pageview_company[SessionId]) 
                                ) 
                            )
 
 
Duration_Calc = [Duration_Max] - [Duration_Min]

 

 

I've got a tab like that and would like to get the average session time by 'PlaformEntityName' which are a Text field with client's name:

 

[URL=https://www.casimages.com/i/181213015203273498.png.html][IMG]https://nsa39.casimages.com/img/2018/12/13/mini_181213015203273498.png[/IMG][/url]

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi SRK_23

    I'm uploading back your file here with the measure i posted earlier included.

  • AlB's avatar
    AlB
    7 years ago

    Hi SRK_23

    Yeah, it's correct. That's the same. I said earlier that the measure shows the result in minutes

    2,37 minutes is 2 mins and 22 seconds, 2:22

10 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi SRK_23

     

    I'm not sure I fully understand what you need but wouldn't the following be sufficient?

     

    1. Set PlatfomEntityName in the rows of a matrix visual 

    2. Set in values of the matrix a measure similar to:

              AverageSessionTime = AVERAGE(Table1[Duration_Calc])

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi SRK_23,

         

        What about converting your calculated column Duration_Calc to be seconds and then calculate the average?

         

        You could convert the calculated column Duration_Calc to be seconds with the formula below.

         

        seconds =
HOUR ( 'dm_pageview_company'[Duration_Calc] ) * 360
    + MINUTE ( 'dm_pageview_company'[Duration_Calc] ) * 60
    + SECOND ( 'dm_pageview_company'[Duration_Calc] )

        Measure = AVERAGE(dm_pageview_company[seconds])

        After you calculate the average of the sceonds, you could convert the seconds to the hh:mm:ss format with the formula below.

         

        average_hhmmss=
INT ( 'dm_pageview_company'[Measure] / 3600 )
    & ":"
    & RIGHT (
        "0"
            & INT (
                (
                    'dm_pageview_company'[Measure]
                        - INT ( 'dm_pageview_company'[Measure] / 3600 )
                            * 3600
                )
                    / 60
            ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD ( 'dm_pageview_company'[Measure], 3600 ), 2 )

         

        Best Regards,

        Cherry