Forum Discussion
Dynamically calculate Average
- 4 years ago
Thank you for your reply. I have actually edited the query and transposed the data in a way I get month-year and all the activites from a single query. I am no longer using the Date Query. Instead, I have used group_by to group it based on the activity dates and get the sum for each activities. Next step, I put conditional columns to get the number of sessions occured in a month-year by checking the sum column of that particular activity - i.e. if the sum is > 0 the output in conditional column in 1. Hence I get the number of sessions occured in a month - year. After that, I transposed the query and got columns as month-year and all activities in the row. Then I calculated Average using this Power Query - each List.Average(Record.FieldValues(_ & [Session Type = null])). It did work perfect!
Yes, using Matrix I do get the average but the format is messed up. Plus, when we expand the exercise with year and months the average is not accurate. Below are the attached snapshots of it.
Thank you, Theo!
Also, I am not understanding how the average for the month is incorrect? The Average column that you first presented (highlighted in red below) is the Total Average for All Months combined. It is not at the "month" level.