# Power BI Exchange

View Only

## DAX for Average Total in Pivot Table

• #### 1.  DAX for Average Total in Pivot Table

Posted Jul 23, 2021 02:05 PM

PowerBI Community,

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

Attachment(s)

AVERAGE_Example.xlsx   186 KB 1 version

• #### 2.  RE: DAX for Average Total in Pivot Table

Top Contributor
Posted Jul 23, 2021 02:41 PM
Hi Andrew:

If you sum up your questions and answers and then use the DIVIDE function the information can be then shown in a matrix. I'll attach an example.

Hope this helps.

Bill

------------------------------
William Skelley
01106
8602807221
------------------------------

Attachment(s)

averages.pbix   19 KB 1 version

• #### 3.  RE: DAX for Average Total in Pivot Table

Top Contributor
Posted Jul 26, 2021 02:06 PM
Hi Andrew:

I will show what I had, which is not very complicated and I hope this is what will help you. BTW I only used a few basic measures and put the results in a matrix. I'll attach a couple of photos for your reference.

Thanks!

Total Den = SUM(Data[DEN])

Total Num = SUM(Data[NUM])

Average = DIVIDE([Total Num], [Total Den],0)

------------------------------
William Skelley
01106
8602807221
------------------------------

• #### 4.  RE: DAX for Average Total in Pivot Table

Posted Jul 27, 2021 12:47 PM

Bill,

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.

Andy