cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
procyon82 Member
Member

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Averages

Right, sorry, you have to use aggregates in a measure, so the proper formula would be:

 

Pages/Session = DIVIDE(SUM([Pageviews]),SUM([Sessions]),0)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


7 REPLIES 7
Super User
Super User

Re: Averages

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


procyon82 Member
Member

Re: Averages

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

  

 

Super User
Super User

Re: Averages

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


procyon82 Member
Member

Re: Averages

If I do that it tells me: The value for 'Pageviews' cannot be determined. Either 'Pageviews' doesn't exist, or there is no current row for a column named 'Pageviews'.

 

If I specify the table name I get: 

A single value for column 'Pageviews' in table 'GA Traffic' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

I think the solution is to use DAX, so I tried this formula, which gives me the same average that is calculated from daily values instead of the sum of all those values:

 

Pages/Session Measure = SUMX('GA Traffic',DIVIDE('GA Traffic'[Pageviews],'GA Traffic'[Sessions],0))/COUNTROWS('GA Traffic')

 

I'm not sure what I need to do to fix that.

Super User
Super User

Re: Averages

Right, sorry, you have to use aggregates in a measure, so the proper formula would be:

 

Pages/Session = DIVIDE(SUM([Pageviews]),SUM([Sessions]),0)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


v-haibl-msft Super Contributor
Super Contributor

Re: Averages

@procyon82

 

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" )

Averages_1.jpg

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 )

Averages_2.jpg

 

Best Regards,

Herbert

procyon82 Member
Member

Re: Averages

@Greg_Deckler That did it! Thanks a lot.