topic Re: Averagex and other "x" functions in DAX Commands and Tips

Averagex and other "x" functions

nsadams87xx — Fri, 01 Nov 2019 17:29:46 GMT

Hey Everyone,

I've been working with Power BI for about a year now and I keep seeing and reading articles and contributions about how averagex() and other X functions are so important and fundamental to smart development. However I haven't really come across a situation where I feel like average() wouldn't work for me. I feel like I may be misunderstanding what averagex does then. My understanding of averagex() is this:

Averagex() will take an expression such as sum() and then average those sum() results over an entire table such as dates. So would an applicable example of that be the average order of total part numbers ordered per day? Or even per Month for that matter if I decided to use value('month') in place of a static table?

Re: Averagex and other "x" functions

Nathaniel_C — Sat, 02 Nov 2019 00:22:22 GMT

Hi nsadams87xx ,

[Total Sales including tax] = SUMX(Sales, Sales[Extended Amount]+Sales[Tax Amount])

When to use iterators rather the aggregators such as SUM()

When the data doesn't contain the line total. If you have table[Price Per Unit]*table [Quantity] and there is no column that contains that product. You can't sum both columns and then multiply them. Use SUMX.

However if there is a total column for each line, like [Total Sales], then you could use SUM(table[Total Sales])

These are two brief examples. Sometimes you can use SUMX () instead of CALCULATE(). Probably a lot of it is personal preference, however it might be worth it to learn the X functions and add another arrow to your quiver. There are also a lot of discussions in blogs and YouTube that may be worthwhile to look at for your edification.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Averagex and other "x" functions

nsadams87xx — Mon, 04 Nov 2019 14:35:57 GMT

Thank you Nathaniel. I think I was relatively on the right track then. I have always used calculate because I feel like I had more control over my data like that. Basically the entire time I have been thinking "what's really the difference between sumx or averagex and using calculate using sum or average?"