Averages

[procyon82]

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?

[v-haibl-msft]

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

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

[Greg_Deckler]

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.

[procyon82]

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

[Greg_Deckler]

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.

[procyon82]

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.

[Greg_Deckler · Accepted solution]

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

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

[procyon82]

@Greg_Deckler That did it! Thanks a lot.