Forum Discussion

jeffreykeryk's avatar
jeffreykeryk
Icon for Helper I rankHelper I
10 years ago
Solved

Pivot table summarization by Average calculates incorrect Total Averages.

This pivot is summarized by Average. The body of the pivot Averages correctly. The totals are whack. (Technical term).

 2014 Q1 Average should be 1,916,497.61

Need Help
  • greggyb's avatar
    greggyb
    10 years ago

    Blanks are ignored in DAX. If 0 is represented in your data by a null / blank then that row will not be considered in the average in the numerator or denominator.

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    What should the averages be?  Do you want to average all values that contain 0 for that month?  

    • jeffreykeryk's avatar
      jeffreykeryk
      Icon for Helper I rankHelper I
      10 years ago

      Using "2014 Q1" as an example.

      There are 3 values that total to $5,749,493.

      ($5,749,493 / 3) = $1,916,497.6

      FYI, this is how Excel computes Average.

       

      Thanks in advance.

      Jeff Keryk

      • greggyb's avatar
        greggyb
        Icon for Resident Rockstar rankResident Rockstar
        10 years ago

        If using the default average aggregation, the resulting measure is equivalent to defining a measure as follows:

         

        // DAX
// Measure

Average =
AVERAGE( 'Table'[Field] )

        This will add up every value in the field and divide by the count of values. If you have granular data, we should not expect the grand total average be the same as the sum of the displayed subtotals divided by the count.

         

        The AVERAGE() function is equivalent to the following:

        // DAX
// Measure

Average2 =
SUM( 'Table'[Field] ) / COUNT( 'Table'[Field] )

        So, if you have more entries in your data model table than display fields in the matrix, then your grand total will not be simply the sum of displayed values divided by the count.

         

        Can you share your model structure and / or some sample data recreating this issue with us?