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]) , F...
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

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

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi SRK_23

    I see that v-piga-msft might have already given you a solution but since I'd already had a look at your file, I'll post mine too. Just in case it can be of help.

    With the table structure and the columns that you currently have, I would place PlatformEntityName (and potentially SessionId under it, if you need it) and place the below measure in values. It will give you the average duration in minutes. You can change to Mins-Secs format from that if needed.

     

    AverageSessionTime =
AVERAGEX (
    VALUES ( dm_pageview_company[Duration_Calc] );
    dm_pageview_company[Duration_Calc]
)  
   *24*60

     

     

     

     

  • SRK_23's avatar
    SRK_23
    Helper I
    7 years ago

    Should I tipe the code like this in a measure ? Because the measure is not recognized and it doesn't work. Sorry i'm quite new with those calculations.

     

    I put the file here:

     

    Link sendbox

    • AlB's avatar
      AlB
      Community Champion
      7 years ago

      Hi SRK_23

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

      • SRK_23's avatar
        SRK_23
        Helper I
        7 years ago

        Hi AlB , I did your calculation here and the average seems wrong, or maybe I don't understand something. In the example attached, we have a session length at 2 minutes 22 secondes, and the average on the session is 2.37 in your calculation; the average should be 2:22 as well (as we focuse on only one session). Do you know what I mean ?

        (File attached)