Forum Discussion
Resolver IAVERAGE OF TOTAL SUMS
I have a google analytics table, which brings page views per date, per city. I have a column in this table called "Pageviews", and some custom columns i made to identify the month number of the row.
I want a way of determining the monthly sums (no problem in visuals) and the monthly average across all the years.
Based on another post here, I tried this:
Sums of All Website Data = AverageX(
Summarize('All Web Site Data', 'All Web Site Data'[Month Number]),
Calculate(sum('All Web Site Data'[Pageviews]))
)But this gives me an error that "The expression specified in the query is not a valid table expression.
I would appreciate some advice. Thank you!
Hi Unicorn_Tech ,
Given the possibility that multiple years will occur, I created a calculated column to identify the year and month.
YearColumn = FORMAT([Date],"YYYY-MM")A calculated column to return the monthly average values.
MonthlyAverageColumn = CALCULATE(AVERAGE('Table'[Pageviews]),FILTER('Table',[YearColumn]=EARLIER('Table'[YearColumn])))A measure to return the monthly average values.
MonthlyAverageMeasure = CALCULATE(AVERAGE('Table'[MonthlyAverageColumn]),FILTER(ALLSELECTED('Table'),[YearColumn]=MAX('Table'[YearColumn])))If you still have doubts, please provide some sample data and what to expect. Take care to protect your privacy.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Unicorn_TechAnd just to add, this quick measure:
Pageviews average per Month Number = AVERAGEX( KEEPFILTERS(VALUES('All Web Site Data'[Month Number])), CALCULATE(SUM('All Web Site Data'[Pageviews])) )Is returning the sums for the month, rather than the average.
Hi Unicorn_Tech ,
Given the possibility that multiple years will occur, I created a calculated column to identify the year and month.
YearColumn = FORMAT([Date],"YYYY-MM")A calculated column to return the monthly average values.
MonthlyAverageColumn = CALCULATE(AVERAGE('Table'[Pageviews]),FILTER('Table',[YearColumn]=EARLIER('Table'[YearColumn])))A measure to return the monthly average values.
MonthlyAverageMeasure = CALCULATE(AVERAGE('Table'[MonthlyAverageColumn]),FILTER(ALLSELECTED('Table'),[YearColumn]=MAX('Table'[YearColumn])))If you still have doubts, please provide some sample data and what to expect. Take care to protect your privacy.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.