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!
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: