Quick Introduction
Do you know syntax sugar? May be not, but you know Sugar. It’s sweet.
Writing DAX Measures usually require the use of Functions. The group of functions that always make it into Measures are the aggregator functions.
Why not? Unlike Calculated Tables and Calculated Columns, Measures are typically used for reporting, on visuals. And reports are nothing but data summarized. Summarizing data usually takes any form of:
- Getting totals (SUM)
- Finding an average (AVERAGE)
- Getting the highest (MAX)
- Getting the lowest (MIN)
- Counting (COUNT, COUNTA)
Therefore, most of reporting in Power BI will have to use any of the functions above in one way or the other.
Every report that has to do with totaling up the values of a column for example will include the SUM function.
The group of functions listed above are what we refer to as Aggregator functions.
However, for each one of those functions, there are variations of them carrying “X” suffixes.
As we have SUM, we have SUMX. There is AVERAGE and there is AVERAGEX. The same goes for the other ones hence the presence of MAXX, MINX, COUNTX and COUNTAX functions in DAX.
So, what makes the two groups different? What should inform the choice of which to use? For example, when should I use SUM versus when should I use SUMX?
While this post used SUM and SUMX as examples, the same principles applies to the other aggregators and their “X” variations.
SUM vs SUMX
The SUM function has a very simple task. To add up all the numbers in a column. A single column. It has just one argument.
SUMX on the other hand has 2 arguments. It’s function goes beyond adding all the numbers in a column.
- SUMX gets access to the table supplied to it inside the first argument
- Performs whatever expression specified in the second argument for each row of that table
- Then sums up the result of that expression.
Let’s try some examples
Demonstration
In this demo, I will start by presenting a simple case for SUM and SUMX. And to prove a point, I will start with SUMX.
The task at hand is to get the total Sales from our Sample Data. Download the Sample Data Here.
- Create a new measure named Total Sales 1
- Type the formula SUMX(Orders, Orders[Sales]) and hit the Enter key
- Visualize the Total Sales 1 Measure by Category on a Table Visual
The first argument in SUMX requests a table, over which the expression in the second argument can be evaluated for each row. The results of that expression is now summed up.
That seems like an overkill just to get total Sales. So, DAX provides a sweeter way to achieve the same thing. A Syntax Sugar, A shortcut. The SUM function.
To get the exact same result as above:
- Create a new Measure named Total Sales 2
- Write the measure SUM(Orders[Sales])
- Add the result of the measure to the same table in the existing report
SUM is simpler
Both SUM and SUMX have produced the same result. But clearly, SUM took a shorter and sweeter method to produce it’s own result.
When you write a SUM(Orders[Sales]), DAX internally uses SUMX(Orders, Orders[Sales]).
Seen the sugar?
SUM did not have to collect a table, process an expression for each row on the table before summing them up. It just collects a column and sums up the values in that column.
Does that make SUM a better option to SUMX? The answer is: It depends.
For a simple case as the one described earlier, SUM is an easier option to use, read and understand.
SUMX is more powerful
SUMX offers some other benefits that are not achievable with SUM. A shortcut route will save you time getting to your destination and will also be a simpler option.
But an ideal route is always open to other benefits of exploration
Recall that the only argument in SUM is a Column Name.
The fact that SUMX has a Table as an argument opens ways to manipulate tables in our formulas. The table can be a physical table in the model (like the earlier SUMX example), or a virtual one created on the fly with any Table function in DAX.
This allows the function the ability to perform expressions for each row of the table supplied.
Manipulating Tables with SUMX
For example, instead of getting the Total Sales against the entire Orders Table, with SUMX we can do the summation on a portion of the data alone. Say we are considering Organic Sales alone.
Therefore, we can replace the first argument in our formula with a filtered table instead. DAX FILTER function does just that. It returns a filtered table.
The FILTER function is a Table function that collects a Table as it’s first argument, then filters the table based on the expression specified in the second argument.
To create a Total Sales for only Organic Sales Rep for example, rather than the full Sales Data, the first argument (Table) of SUMX can be replaced with a filtered table using the FILTER function.
Working beyond One Column with SUMX
Another thing is, you should have noticed from the SUM syntax that it only takes Column Name as an argument. NOT Column Names. Meaning that, we can only work with a single column at a time.
The Second argument of SUMX (Expression) allows us to express ourselves beyond a single column or beyond just the values on a column. For example, if we wanted to calculate Total Cost. The data does not include a field for Cost. But we can derive it by subtracting Profit from Sales.
To calculate Total Cost using SUMX, we can write our formula like:
SUMX(Orders, Orders[Sales] – Orders[Profit])
Conclusion
While the SUM function is straight forward to use, the SUMX function is the real deal. Because with it, we can control things better.
We are not restricted by having to use full tables, neither are we restricted to using only single columns.
SUM is simple enough, next time you see SUMX, you should be able to interpret what is happening there.
