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!
Hi NKotak_Leecare ,
I recommend matrix table and Date Hierarchy to reach that.
First, you need a calendar table or date column, this will be used for date hierarchy.
Second, create a measure like the following:
Measure =
IF(
HASONEVALUE( 'Table'[Date].[Month] ),
"expression of total",
IF( HASONEFILTER( 'Table'[Date].[Year] ), " expression of avg this year", "expression of avg all years" )
)
Please change the "expression" part to calculate what you want. For example, avg = AVERAGE('Table'[Value]).
You can calculate total or avg group by year , month via drill down ( marked in red).
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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!
Hi NKotak_Leecare ,
I'm glad you've solved your problem. Please mark it as a solution and anyone who has the same problem as you will soon find the answer.
Best Regards
Community Support Team _ chenwu zhu