Answered by:
SSAS Tabular - (strange) behaviour of AVERAGE and AVERAGEX

Question
-
Hi,
i'm seeing what i think is a strange behaviour in the calculation of an average using DAX' AVERAGE and AVERAGEX.
This is the manual calculation in DW, using SQL:
In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.
i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
Do anyone understand what could be happening?
Best Regards
- Edited by Lrmmf_ Sunday, June 21, 2015 6:34 PM
Sunday, June 21, 2015 6:30 PM
Answers
-
Hi Darren,
effectively, when i click the dropdown arrow on each column, this one is the only where BLANKS() appear
I tryed
Frontend Marging Percent ACY 2:=AVERAGEA([_Frontend Margin Percent ACY])/100
which supposedly would count blanks, but the result is exactly the same, seems that i can only calculate average
using the rough SUM([_Frontend Margin Percent ACY])/COUNTROWS(....)
- Proposed as answer by Simon_HouMicrosoft contingent staff Monday, June 22, 2015 5:36 PM
- Marked as answer by Simon_HouMicrosoft contingent staff Tuesday, June 30, 2015 2:06 AM
Monday, June 22, 2015 8:48 AM
All replies
-
Are you using a % number format for these measures? This would explain the need to divide by 100 as the % format will shift the decimal place 2 points to the right ( displaying a value of 0.5 as 50%)
In terms of the calculation AVERGAGE and AVERAGEX both ignore null & blanks (as per their documentation https://msdn.microsoft.com/en-us/library/ee634231.aspx?f=255&MSPPError=-2147217396 ) which should explain the difference when comparing the result to SUM(...) / COUNTROWS
http://darren.gosbell.com - please mark correct answers
Monday, June 22, 2015 3:54 AM -
Hi Darren,
effectively, when i click the dropdown arrow on each column, this one is the only where BLANKS() appear
I tryed
Frontend Marging Percent ACY 2:=AVERAGEA([_Frontend Margin Percent ACY])/100
which supposedly would count blanks, but the result is exactly the same, seems that i can only calculate average
using the rough SUM([_Frontend Margin Percent ACY])/COUNTROWS(....)
- Proposed as answer by Simon_HouMicrosoft contingent staff Monday, June 22, 2015 5:36 PM
- Marked as answer by Simon_HouMicrosoft contingent staff Tuesday, June 30, 2015 2:06 AM
Monday, June 22, 2015 8:48 AM