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

Answer 1

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

Answer 2

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(....)