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.
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.
Solved! Go to 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 )
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 )
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |