Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX for Average Total in Pivot Table

  • 1.  DAX for Average Total in Pivot Table

    Posted Jul 23, 2021 02:05 PM
      |   view attached

    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)

    xlsx
    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
      |   view attached
    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
    Longmeadow MA
    8602807221
    ------------------------------

    Attachment(s)

    pbix
    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
    Longmeadow MA
    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