locked
DAX AverageX function woes. RRS feed

  • Question

  • =AVERAGEX(all(table[field]),table[number]) always just returns 'number', regardless of what field I want the average to be calculated over.  Even tried =averagex(VALUES(table[field]),Calculate(SUM(table[number]))).  Nothing but number returned.   Even if the table specified is a different tab.  Why?

    • Edited by AbbeyNormal Friday, December 18, 2015 10:38 PM
    Friday, December 18, 2015 10:03 PM

Answers

  • No, AVERAGEX evaluates an expression for each row of a table, and returns the average of all results.

    So, using AVERAGEX, in your case the table is all rows with the same Class:

    FILTER(ALL(Table),Table[Class] = EARLIER([Class])

    and the expression is just the value of [Number]. So the calculation below should return the results:

    =AVERAGEX(FILTER(ALL(Table),Table[Class]=EARLIER([Class])),[Number])

    • Marked as answer by AbbeyNormal Monday, December 21, 2015 11:00 PM
    Monday, December 21, 2015 9:13 PM
    Answerer

All replies

  • This indicates that the sum of Table[Number] divided by the number of rows in Table is equal to Table[Number].

    Without knowing even the slightest details about your model or use case, it's impossible to help in any way besides pointing out the definition of the function.

    GNet Group BI Consultant

    Saturday, December 19, 2015 10:48 PM
  • This indicates that the sum of Table[Number] divided by the number of rows in Table is equal to Table[Number].

    Without knowing even the slightest details about your model or use case, it's impossible to help in any way besides pointing out the definition of the function.

    GNet Group BI Consultant

    Hi Abbey,

    Following on from what Greg has mentioned, can you kindly provide some sample/test data so that we can reconstruct this scenario and work towards a solution. Details of the table structure and some representative dummy data (10-15 rows) should be sufficient.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Sunday, December 20, 2015 8:56 AM
  • Hi Abbey,

    try ALL(Table) instead of ALL(Table[Field]). I was able to reproduce the behavior you mention and changing this solved it.

    The reason why this happens, is subtle: both ALL(Table) and ALL(Table[Field]) return all rows. The difference is that in ALL(Table), the rows contain all columns from Table, but with ALL(Table[Field]), the rows contain only the Field column. When you reference Table[number], it cannot be found in the ALL(Table[Field]) table and the value from the current row will be used instead; and the average will obviously be this value. The only situation in which this works, is with:

    =AVERAGEX(ALL(Table[Number]),Table[Number])

    In this case, the ALL(...) does contain the column to average, and you will see that it returns the average of this column on every row. Not that this would be the best way to do this, a simple =AVERAGE([Number]) would suffice.

    Monday, December 21, 2015 8:56 AM
    Answerer
  • Thank you, but then it returns the same average number for every row in the table.    This is equivalent to using =AVERAGE(number).  What I want is an average based on each distinct value in table[Field], which is what I assume AVERAGEX is supposed to do.

    For example:

    Class Number AverageX (assumed/desired) Actual

    A 1 3 1

    A 3 3 3

    A 5 3 5

    B 6 7 6

    B 8 7 8

    I assume =AverageX(all(table[Class]), table[Number]) would return the AVERAGEX results desired above.

    Thanks for all replies.

    Monday, December 21, 2015 2:57 PM
  • Note the table got scrunched.  I want the average by Class.  So 3 for all rows of Class A (1+3+5)/3, and (6+8)/2 = 7 for rows of Class B. What I get from my AverageX formula is equal to the 2nd column, which is useless.  If I add All(table), its just one lump sum average, which is also useless.
    Monday, December 21, 2015 3:04 PM
  • Hi Abbey,

    You could try either of these solutions:

    =
    AVERAGEX(
      CALCULATETABLE(
        TableName,     
        ALLEXCEPT(TableName, TableName[Class])
      ),   
      TableName[Number]
    )


    Or

    =
    CALCULATE(
      AVERAGE(TableName[Number]),   
      ALLEXCEPT(TableName, TableName[Class])
    )

    In both cases we are combining CALCULATE/CALCULATETABLE with ALLEXCEPT to expand the context of the average to that of all rows that are for the same class. You can read about the different contexts in DAX here.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, December 21, 2015 3:36 PM
  • Thank you, but both of those just return a number which is neither the average by class, or the overall average.  It is much larger, but I can't figure out what it is calculating.  Any other ideas on how to calculate an average for each unique value of a column?  Isn't that what AVERAGEX is supposed to do?    
    Monday, December 21, 2015 5:11 PM
  • No, AVERAGEX evaluates an expression for each row of a table, and returns the average of all results.

    So, using AVERAGEX, in your case the table is all rows with the same Class:

    FILTER(ALL(Table),Table[Class] = EARLIER([Class])

    and the expression is just the value of [Number]. So the calculation below should return the results:

    =AVERAGEX(FILTER(ALL(Table),Table[Class]=EARLIER([Class])),[Number])

    • Marked as answer by AbbeyNormal Monday, December 21, 2015 11:00 PM
    Monday, December 21, 2015 9:13 PM
    Answerer
  • Thank you, that works!  

    I now wonder if I have chosen the wrong tool given the complexity.  If I need to calculate a dozen of these columns would an alternative technology like using the average of a measure in a PivotTable, where this will end up anyway, or Analysis Services/Cube/MDX be better?


    • Edited by AbbeyNormal Monday, December 21, 2015 11:13 PM
    Monday, December 21, 2015 11:03 PM
  • Well, I strongly prefer solving this in a measure instead of a calculated column. A measure is evaluated in the context you use it in, so in a pivot table with [Class] as row labels, a simple average will do the trick:

    AvgNumber:=AVERAGE(Table[Number])

    If you want to use the measure in other contexts, like a pivot table with [Field] in the row labels, a calculation like Michael showed will do:

    AvgNumberByClass:= CALCULATE(AVERAGE(Table[Number]),ALLEXCEPT(Table[Class]))

    One advantage of measures is that they don't take up space in the model, as calculated columns do. So, you have definitely not chosen the wrong tool, but you could use it in a better way :-)

    Tuesday, December 22, 2015 8:36 AM
    Answerer
  • Power Pivot is an Analysis Services cube. Power Pivot is exactly the SSAS Tabular engine.

    MDX would likely have a solution of similar complexity.

    Like Michiel said, this is likely the misapplication of a calculated column and the problem seems (though without a clear specification of the problem, it's difficult to say) amenable to being solved with a measure, instead.

    GNet Group BI Consultant

    Tuesday, December 22, 2015 3:24 PM