Forum Discussion
Obtaining an average Measure
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.
"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