Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 s...
Need Help
  • MattAllington's avatar
    MattAllington
    7 years ago

    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 )
MattAllington's avatar
MattAllington
Community Champion
7 years ago

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 )