Forum Discussion
jofoster
Microsoft Employee
Microsoft EmployeeDaily average across categories
Seems like this should be dead simple, but can't figure it out. My data set has multiple values per day. using the data set below, I want to be able to calculate the daily average by dayofweek or isUserINternal or across both of those parameters. Using a regular average, it seems to be treating each row as distinct, so in the below, it sees 7 rows, where I want it to be 3.
Thanks
| EventTimestamp | dayofweek | IsUserInternal | Interactions | Prompts | Responses |
| Day1 | 3 | 0 | 583 | 1875 | 120 |
| Day 1 | 3 | 1 | 107 | 354 | 15 |
| Day 2 | 4 | 0 | 971 | 2947 | 206 |
| Day 2 | 4 | 1 | 157 | 462 | 23 |
| Day 2 | 4 | 0 | 1 | 0 | |
| Day 3 | 5 | 0 | 818 | 2698 | 179 |
| Day 3 | 5 | 1 | 105 | 349 | 11 |
2 Replies
- jdbuchanan71
Super User
Hello jofoster
You just need to feed it the 3 rows you want it to average over like so:
Avg Interations = AVERAGEX ( VALUES ( 'Table'[EventTimestamp] ), CALCULATE( SUM ( 'Table'[Interactions] ) ) )
The VALUES ( 'Table'[EventTimestamp] ) returns a table that looks like this:
Day 1 Day 2 Day 3 Then the sum of the interations is calculated for each row of that table and finally the average is calculated.
- Ashish_Mathur
Hi,
Show your expected result.
