Forum Discussion
Dynamically calculate Average
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, you are right. The average is incorrect in the first mock. Ideally, the Average for Bed Exercise should have been 13/8, where 8 is the total number of months. But we are not getting that there. Here is the outcome of Average taken as measure which is still incorrect.
The other pic is how we ideally want the outcome to be like:
I think the following measure will provide you what I believe you are after:
Measure = CALCULATE ( AVERAGE ( 'Table'[Amount] ) , ALLEXCEPT ('Table' ,'Table'[Session Type] ) )
Hope this helps.
Theo
Yes, I tried it but the outcome is same as before. Here is the attached snapshot of it.
Can you please provide a snapshot of the Matrix table and the values you are using in each of the fields? I think there is some kind of anomaly in what you are wanting in your output versus the current structure of fields being brought into your model.
Thanks,
Theo
Required details: