cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nsadams87xx Member
Member

Averagex and other "x" functions

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Averagex and other "x" functions

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

View solution in original post

2 REPLIES 2
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Averagex and other "x" functions

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

View solution in original post

nsadams87xx Member
Member

Re: Averagex and other "x" functions

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?"

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)