Forum Discussion

Unicorn_Tech's avatar
Unicorn_Tech
Resolver I
5 years ago

Obtaining an average Measure

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.  M...
Need Help
v-yingjl's avatar
v-yingjl
Community Support
5 years ago

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's avatar
    Unicorn_Tech
    Resolver I
    5 years ago

     

    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's avatar
      HotChilli
      Community Champion
      5 years ago

      "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