Higher grain Summarization

[dhamodarankms]

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

[Anonymous]

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.

[Anonymous]

https://dax.guide/average/

[amitchandak]

@dhamodarankms , check if these can help

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/88878bf3-7894-445b-b3c1-740329fa695b/dax-av...

https://www.decisivedata.net/blog/understanding-using-x-functions-dax

[Greg_Deckler]

@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.