Display Averages

[Anonymous]

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.   

[MattAllington · Accepted solution]

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 )