View My Drafts
I am trying to come up with a DAX expression that calculates the average of two averages for each row in a pivot table and then the average of the totals. I have used the AVERAGEX to get the averages of the rows but can't figure out the Total syntax. Possibly using the SUMMARIZE function?
Sample file is attached.
Any help would be greatly appreciated.
Andrew R. Parker
Total Den = SUM(Data[DEN])
Total Num = SUM(Data[NUM])
Average = DIVIDE([Total Num], [Total Den],0)
Thanks for the help.
Unfortunately, you have landed upon the reason why this issue is so troublesome – you can't just sum numerator and denominator for all questions. You have to average the results of each question. This becomes very apparent when there are many more responses to one question than the other.
I still need an automated way to average the results of each question and calculate a total result which is an average of totals.
Going with brute force Excel method for now.