Obtaining an average Measure

[Unicorn_Tech]

Via Google Analytics, I have the average time, per date per country a user spent on a page.  As you can see by the below picture, the time on page is not averaging the total of the table elements.  Meaning if I do not have Country in there, I expect it to average all the Time on Page of "Exclusive Area", rather than itemize it.  If I do add Country, I expect it to average all the time on the page, per page per country.

Is there a way to do this?

[v-yingjl]

Hi @Unicorn_Tech ,

"Meaning if I do not have Country in there, ... If I do add Country, ..."

Based on your description, seems not certain what did add Coutry/ have country represent, add fields in the table visual or anything else?

Basically, if you want to calculate the time average for each page, you can create this measure:

Measure =
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[Time on Page] ),
        ALLEXCEPT ( 'Table', 'Table'[Page Title] )
    )
RETURN
    FORMAT ( _avg, "H:MM" )

If not help, maybe you can consider more information about this issue for further discussion.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Unicorn_Tech]

My goal is to create a visual of pages, and their average time spent on them.  I would like a slicer, per country, so that the measure can be adjusted per country.

My table has the following columns:

Average Time on Page, Country, Date, Entries, Exits, Page, Pagetitle, Pageviews, Time on Page, Unique Page Views, Users.

My problem is consolidating this table - it repeats the Page for every date and Country, rather than giving me averages over a span.  Yet I couldn't see a way to limit the info to a particular time period otherwise.  If I pull the table again, without date, I will be left with the Average Time on Page and Time on Page, per page, per country.  Which is fine, but obviously I'm interested in recent data, rather than old data.

I would appreciate any advice.

[HotChilli]

"My problem is consolidating this table - it repeats the Page for every date and Country"

Yes, I'm reasonably sure we know what the problem is.  It's not really repeating the page for every date and Country - powerbi doesn't really know how to aggregate time values unless you help it.

So Greg has posted some links where you can convert time to seconds and treat it as a numeric type.  Once the value is numeric , it can be aggregated (average, sum, whatever)

Yingjie Li has given you the technique which (behind-the-scenes) aggregates the time value as a decimal and then gets explicitly formatted as text.  This should work in the table you show if you remove 'Time on page' from the visual.

If that works ok, you can set up slicers for date and country

[Greg_Deckler]

@Unicorn_Tech Perhaps you need time aggregation? 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[HotChilli]

Check the datatype.  If it is Time, you can't average half past one and quarter past three