Forum Discussion
Higher grain Summarization
Dear DAX community,
I want to understand the difference between AVERAGE and AVERAGEX in terms of working method and capacity.
The formula for the average is SUM(amount)/Number of Instance. AVERAGEX works at row context, it is calculating the Average at every row Level. I want to understand, how this is happening?
Thanks for you time and much appreciate your support in this regard.
Regards,
SD
4 Replies
- Greg_Deckler
Community Champion
dhamodarankms So AVERAGE accepts a column as an input. AVERAGEX accepts a table or table expression as an input as well as a column specification. So, you can do this:
AVERAGE('Table'[Column]) will give you the average of everything in the table in that column
with AVERAGEX you can do things like:
AVERAGEX(FILTER('Table',[Device] = "A"),[Column])
This allows you to create some kind of filter and then just do an average over that subset of the table and column values.
- AVERAGE and AVERAGEX does not equal to SUM( Amount ) / Number Of Instances. This is a misconception. If [Amount] is BLANK, this amount is not considered. So, fir instance, AVERAGE of (1, 1, BLANK, BLANK) = 2/2, not 2/4. If you want to force the recognition of BLANKs, you have to add 0 to them. So, AVERAGE of (1, 1, BLANK + 0, BLANK + 0) = 2/4.
