cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SRK_23 Regular Visitor
Regular Visitor

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...]

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Calculate Average time

Hi @SRK_23

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

Super User
Super User

Re: Calculate Average time

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 10
Super User
Super User

Re: Calculate Average time

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])

SRK_23 Regular Visitor
Regular Visitor

Re: Calculate Average time

I tryied but the resultat is not correct, could you please have a look to the doc attached here: https://uptobox.com/1k2100scf5z0

 

Thanks,

Community Support Team
Community Support Team

Re: Calculate Average time

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Calculate Average time

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 Regular Visitor
Regular Visitor

Re: Calculate Average time

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.

 

2.PNGI put the file here:

 

Link sendbox

Super User
Super User

Re: Calculate Average time

Hi @SRK_23

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

Highlighted
SRK_23 Regular Visitor
Regular Visitor

Re: Calculate Average time

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)

Super User
Super User

Re: Calculate Average time

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

Super User
Super User

Re: Calculate Average time

Hi @SRK_23

If you wanted the result in the format Mins:secs instead, you could do something like:

 

AverageSessionTime_Mins_Secs = 
VAR __AverageSessionTime= 
AVERAGEX (
    VALUES ( dm_pageview_company[Duration_Calc] );
    dm_pageview_company[Duration_Calc]
)
RETURN
MINUTE(__AverageSessionTime) & ":" & SECOND(__AverageSessionTime)

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 414 members 4,341 guests
Please welcome our newest community members: