Basic Iterators in DAX: AVERAGEX(), MINX() & MAXX()
Table of Contents
Introduction
In the previous Blog, we introduced basic iterators in DAX and explored how SUMX() works behind the scenes. In this blog, we will look at more basic iterator functions and break down how they differ from simple aggregators, so you can make smarter choices when writing your measures.
Using Basic Iterators: AVERAGEX()
AVERAGEX() works on the same principle as SUMX() and other iterator functions. Similarly, AVERAGEX() evaluates the expression at each row and then evaluates the arithmetic mean of the resulting set of values.
Let’s use AVERAGEX() to find the average delivery days:
Basic Iterator: AVERAGEX Tutorial
To use AVERAGEX():
- First, right-click on All Measures table in the Data pane.
- Then, click on New measure.
- Now, type the following formula in the formula bar and press Enter.
Average Delivery Days =
AVERAGEX ( Sales, Sales[DeliveryDate] – Sales[OrderDate] )
In the above expression:
- For each row in the Sales table, the OrderDate is subtracted from the DeliveryDate and the calculated values are stored temporarily.
- The average over all calculated values is produced.
- Make sure both columns are actual Date (not DateTime).
- Go ahead and click on the matrix visual in the visualization pane.
- Drag and drop Year from the Date table to the rows.
- Similarly, drag and drop Average Delivery Days from the All Measures table to the Values.
This gives us:
If you want a deeper understanding of regular functions in DAX that are discussed in almost every Power BI training in Australia, and how they differ from the iterator function in DAX you can check out this helpful breakdown: Power BI DAX Functions – MIN(), MAX(), AVERAGE()
Using Basic Iterators: MINX() and MAXX()
MINX() returns the smallest value after the evaluation of the expression. Whereas, MAXX() returns the largest value after the evaluation of the expression. For both functions the blank values are skipped and TRUE/FALSE is not supported.
Using Basic Itarators MINX():
Let’s use MINX() to find the minimum sales amount for each customer.
Basic Iterator: MINX Tutorial
- Right-click on the Customer table in the Data pane.
- Click on New Column.
- Type the following formula in the formula bar and press Enter.
Minimum Sales per Customer =
MINX ( RELATEDTABLE ( Sales ), Sales[Sales Amount] )
In the above expression:
- The RELATEDTABLE() stores the subset of rows in a virtual table.
- MINX() iterates over this table and finds the minimum value.
- Click on the “$” in the formatting section.
Type “2” in the text box.
Using Basic Iterators MAXX():
Let’s use basic iterations MAXX() to find the maximum sales amount for each customer.
Basic Iterator: MAXX Tutorial
- Firstly, repeat the first two steps in MINX tutorial.
- Now, type the following formula in the formula bar and press Enter.
Maximum Sales per Customer =
MAXX ( RELATEDTABLE ( Sales ), Sales[Sales Amount] )
In the above expression:
1. The RELATEDTABLE() stores the subset of rows in a virtual table.
2. MANX() iterates over this table and finds the maximum value.
- Now, repeat the fourth and fiifth step in the tutorial above.
- Here, click on the matrix visual in the visualization pane.
- Drag and drop the Customer from the Customer table to the rows.
- Similarly, drag and drop Minimum Sales per Customer and Maximum Sales per Customer from the Customer table to the Values.
This gives us:
Key Differences: Aggregators vs Iterators
Now that we have walked through the tutorials for the Basic Iterators in DAX, it is important to understand how they differ from their simple aggregator counterparts.
As previously discussed in the first blog, both aggregators and iterators behave very differently in DAX.
AVERAGE vs AVERAGEX
These functions differ:
- AVERAGE(): Simply calculates the mean value of a column.
- AVERAGEX(): Evaluate for each row, then averages the result of that expression.
MAX vs MAXX
These functions differ:
- MAX(): Returns the highest value from a single column.
- MAXX(): Evaluates a custom expression for each row in a table, then returns the highest calculated value.
MIN vs MINX
These functions differ:
- MIN(): Returns the smallest value from a column with the current filter context.
- MINX(): Evaluates an expression per row in a table, then returns the smallest result of those calculated values.I
Further Reading
If you want a deeper understanding of basic aggregator functions, you can check out this helpful breakdown: Basic DAX Functions MIN MAX and AVERAGE.
To learn more about Basic Iterations and how they differ from aggregator functions, check out our blog: Basic Iterators in DAX: SUMX()
Conclusion
Understanding iterator functions is a major milestone in DAX. Now that you have seen how MINX(), MAXX(), and AVERAGEX() work, you have gained so much control over your measures and can solve more realistic business problems in Power BI. Now you have the precision and flexibility needed to build smarter and more insightful Power BI models.
FAQs
If the value you want to compare isn’t in a single column but needs to be calculated for each row, use MINX().
Use MAX() when you want to find the highest value of a calculated expression, like profit margin, discount rate, or KPIs that aren’t directly stored in your data. On the other hand, when you only need the highest value from one column, use MAX().
When you need to find the average of ratios, percentages, or formulas, use AVERAGEX(). This is true for anything that needs to be calculated per row. When you want the average of a simple number column, use AVERAGE().
Further Learning and Resources
To learn more about Power BI, check out our course here
Want to learn more about Power BI and how to make reports more efficiently?
Power BI training in Australia: We offer comprehensive training sessions across various cities, including :
Power BI Crash Course eBook: Download our detailed guide to take your Power BI expertise to the next level, covering topics from basics to advanced with practical examples and step-by-step instructions. (Limited time offer)
Download the Beginner’s Guide to Power BI
Ready to enhance your Power BI skills? Sign up for our training courses and explore our wide range of resources designed to elevate your data analytics capabilities.
Contact Us: Make an enquiry about our Power BI courses and find the proper training for you.
We offer comprehensive Power BI training across Australia, tailored to suit different learning styles and proficiency levels. We deliver all our training in collaboration with Dynamic Web Training.
