locked
SSAS Tabular - (strange) behaviour of AVERAGE and AVERAGEX RRS feed

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

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

    Monday, June 22, 2015 8:48 AM