Forum Discussion
Display Averages
I am trying to show average emails sent, received and read by month. I have a query with the columns being Date, Send, Receive, Read. I have read a number of different posts on here but I can't seem to find one that meets exactly what I need. And everything I have tried from the information I did find didn't work.
I know I need to add the "send" column and then divide it by the number of days in the month. So I did create columns for Year, Month and Day. But still can't figure out what measure I need to perform to make this happen.
You should add a calendar table and delete the extra columns you added in your data table.
https://exceleratorbi.com.au/power-pivot-calendar-tables/
Then add the month from the calendar table to your matrix (eg YYYY-MM or similar)
I also recommend unpivoting your data so you have
Date/Attribute/Qty
then you can write measures like this
Total Qty = SUM(Table[Qty])
and put the attribute column as a column in a matrix to see the results.
I guess a formula you could use would look like this
Average Emails = VAR DaysSelected = COUNTROWS ( calendar ) VAR TotalEmails = SUM ( Table[Qty] ) RETURN DIVIDE ( TotalEmails, DaysSelected )
1 Reply
You should add a calendar table and delete the extra columns you added in your data table.
https://exceleratorbi.com.au/power-pivot-calendar-tables/
Then add the month from the calendar table to your matrix (eg YYYY-MM or similar)
I also recommend unpivoting your data so you have
Date/Attribute/Qty
then you can write measures like this
Total Qty = SUM(Table[Qty])
and put the attribute column as a column in a matrix to see the results.
I guess a formula you could use would look like this
Average Emails = VAR DaysSelected = COUNTROWS ( calendar ) VAR TotalEmails = SUM ( Table[Qty] ) RETURN DIVIDE ( TotalEmails, DaysSelected )