Basic Iterators in DAX: SUMX()
Table of Contents
Introduction
If you have been using Microsoft Power BI, you’ve most definitely come across DAX functions. They are generally categorised into two basic types i.e., Aggregators and Iterators. The Iterators functions (SUMX(), AVERAGEX(), MINX(), MAXX(), etc.) are identified by the “X” at the end. On the other hand, the aggregators functions have simple syntax like SUM(), AVERAGE() etc.
Learn more about these simple aggregators: Basic DAX functions sum divide and count.
What are Iterators?
Iterator functions are powerful because they evaluate the expression at every row of the table. Furthermore, iterators allow advanced logic, which is not possible with simple Aggregation.
Some of the common iterators are:
- SUMX()
- AVERAGEX()
- MINX()
- MAXX()
Want to know about the categorisation of DAX functions? Check out our blog on: Functions in DAX
How do Iterators differ from Aggregator Functions?
Feature | Iterators Functions | Aggregator Functions |
Definition | Functions that perform operations row-by-row. | Functions that perform operations on a column / set of values. |
Usage | Used for row context operations. | Used for simple aggregations. |
Syntax Example | SUMX(Table, Expression) | SUM(Column) |
Performance | Can be less efficient for large Datasets. | Generally, more efficient for simple aggregations. |
Context | Requires explicit row context. | Operates in a filter context. |
Flexibility | Allows for complex calculations on each row. | Limited to basic summarizations. |
Result Type | Returns a decimal number. | Typically returns a single value. |
Using SUMX()
In this Blog, we will focus on SUMX() Iterator Funtion only. SUMX() iterates through every row of the table to evaluate an expression and then returns the aggregated result of all the rows.
SUMX() has the following features:
- The table used in SUMX() can be either virtual or physical.
- The expression used for evaluation must always return a number.
- SUMX() ignores blank, logical and text values in a column.
Source: sumx function
Let’s calculate the discount amount for each transaction in the Sales table.
Step-by-Step Tutorial for SUMX()
To use SUMX() for calculating the discount amount:
- First, right-click the table where you want your measure in the Data pane, and then select New measure.
- Here, type the following formula in the formula bar and press Enter.
Discount Amount =
SUMX ( sales, Sales[Unit Price Discount Pct] * Sales[Extended Amount] )
In the above expression:
- For each row in the Sales table, Unit Price Discount is multiplied by Extended Amount, and a temporary computed value is produced.
- SUMX then aggregates all those row results and returns the final discount amount.
- Click on the “$” in the formatting section.
- Type “2” in the text box.
Now, let’s see what happens if we try to calculate the discount amount using aggregators instead of iterators.
- Now, create a New Measure and type the following formula in the formula bar and press Enter.
Discount Amount Wrong =
SUM ( Sales[Unit Price Discount Pct] ) * SUM ( Sales[Extended Amount] )
In the above expression:
- The aggregated value of “Unit Price Discount Pct” from the Sales table is multiplied by the aggregated value of “Extended Amount” from the SalesTable.
Now, click on the matrix visual in the visualization pane.
- Drag and drop Category and Subcategory from the Products table to the rows.
- Similarly, drag and drop Discount Amount and Discount Amount Wrong from your table to the Values.
This will give us:
Notice the difference in values of both measures. Discount Amount evaluates the Discount Amount for each row and then sums the values for each category. Whereas, the Discount Amount Wrong sums the Unit Price Discount Pct and Extended Amount columns and then multiplies them.
Conclusion
Iterators in DAX are what make Power BI truly powerful for dynamic, row-level analysis. Additionally, Iterators can be more computationally demanding than simple aggregations, so use them sparingly and optimise whenever you can. Gaining proficiency with these fundamental iterators will provide you with a solid basis for creating more complex DAX formulas and intelligent Power BI reports.
FAQs
In Power BI, you can use aggregators like SUM() or AVERAGE() to directly summarize a single column of data. Additionally, Iterators like SUMX() and AVERAGEX() are more powerful because they check an expression for each row in a table and then give you a single result.
Yes, sometimes. Iterators like SUMX() and AVERAGEX() check one row at a time, which can however, take longer on big datasets.
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.
