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!
Have you created the Average measure? Measure = AVERAGE ( 'Table'[Amount Column] )
If you drag that into your value field of the Matrix, it should be correct.