Forum Discussion
Helper IPivot table summarization by Average calculates incorrect Total Averages.
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.
Helper IUsing "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
Resident RockstarIf 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?
- Greg_Deckler
Community Champion
jeffreykeryk - To provide a concrete example of what greggyb is saying, consider the following simple math scenario:
1
2
3
12
The average of this is 4.5 (18/4)
4
5
6
The average of this is 5 (15/3)
9.5 / 2 = 4.75 Average
However, if you take the average of all of the numbers 1+2+3+12+4+5+6 = 33 / 7 = 4.7142857...
- jeffreykeryk
- Greg_Deckler
If you add those numbers and ignore the 0, the average of those numbers is 1,932,745.9075.
