Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nsadams87xx
Helper III
Helper III

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
Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors