Forum Discussion
Averages
Hi,
What is the best way to create calculated monthly data in Power BI desktop?
I have a set of daily data and if I let's say want to display the data monthly, it works great until you do a calculation like averages.
Then it looks at daily values again, instead of summing all daily numbers and then dividing. Even if I select 'Average' it shows me the average of the daily values which is close but incorrect.
I was hoping the slicer would handle this properly, but it isn't and it seems almost exaggerated to have to create a new column for every calculation? Am I missing something here?
Right, sorry, you have to use aggregates in a measure, so the proper formula would be:
Pages/Session = DIVIDE(SUM([Pageviews]),SUM([Sessions]),0)
7 Replies
Can you provide some sample data, the results you are getting and what you expect? I am having trouble trying to generate data that can be used to simulate your issue.
I have daily data for pageview and sessions from Google Analytics. I then have a column calculating Pages/Session using this simple formula: Pages/Session = DIVIDE([Pageviews],[Sessions],0)
If I add this to a table it changes it to daily values
http://i.imgur.com/OWfGu0S.png?1
http://i.imgur.com/KZH5ulE.png?1
Try changing your custom column to a measure instead. I believe what you are seeing is that since Pages/Session column exists for every row, it is switching your table back to daily values. Using a measure should fix this.
I assume you have a set of daily data as below. You can create a new column to identify the month and year for each date with following formula.
Month Year = FORMAT ( 'GA Traffic'[Date], "mmmm, yyyy" )
Then you can create a measure which mentioned by Greg_Deckler and drag a Table chart into your canvas as below.
Pages/Session = DIVIDE ( SUM ( 'GA Traffic'[Pageviews] ), SUM ( 'GA Traffic'[Sessions] ), 0 )
Best Regards,
Herbert
