Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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)
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
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.
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.
Right, sorry, you have to use aggregates in a measure, so the proper formula would be:
Pages/Session = DIVIDE(SUM([Pageviews]),SUM([Sessions]),0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |