Contents

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.

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

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
and hit the Enter key**SUMX(Orders, Orders[Sales])** - 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

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

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** 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.

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.

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])**

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.

Translate »

Scroll Up