Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
Proud to be a 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
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?"
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |