What would you do to forMat all values as currency in the Sum of extended price field no matter how you pivot the data?

Level: Beginners/Intermediate (originally posted 2015, updated 2017, 2018, 2022).

This article is one of the most frequently read on my blog site and I wanted to update it to continue to improve the value people get from my site. Even if you have read this article before, why not take another look and refresh your knowledge.  One of the reasons I update these articles is that I am continuously learning. I am constantly learning about the nuances of the DAX language and also learning through the process of teaching at my Power BI Training courses.

Before moving on to SUM() vs SUMX(), there are 2 important concepts about how Power BI and Power Pivot for Excel work that you must understand.

Things You Should Know Before I Cover SUM() vs SUMX()

Filter Behaviour

Before getting into SUM() vs SUMX(), it’s important to know that when you write a formula in DAX, the result of the formula depends on which filters have been applied in the report.  DAX is not the same as Excel. In Excel you can write slightly different formulas in each and every cell of the report and each formula can itself point to different cells creating different results.  Every cell is stand alone and unique.  That is not how it works in DAX.  In DAX you write a single formula such as SUM(Sales[Sales Amount]) and then use filters in the report to modify the results returned from the formula.

Filters can come from:

1. The visuals in your workbook. A Pivot Table if you are using Power Pivot for Excel, or anywhere on the report canvas if you are using Power BI.  The visuals in your report create the Initial Filters that impact your formulas.
2. The use of a CALCULATE() function.  CALCULATE() is the only* function that can change the Initial Filter behaviour of your report.  Of course there may or may not be a CALCULATE() function in your DAX formula (or a precedent formula).  If there is a CALCULATE() function, it can add to, remove from, or modify the initial filter behaviour of the formula.

* Note: Filters can also be modified by an implicit CALCULATE() in a measure, CALCULATETABLE() and also functions that are simplified versions of CALCULATE(), such as TOTALYTD()

Filters always get applied first, then the evaluation is completed.  Filters first, evaluate second. The technical term for this filter behaviour is “Filter Context”, but I prefer to use the term “Filter Behaviour” as it is less intimidating for most people.

Filter behaviour is important because it will affect the results you get from your formulas.  Let’s briefly look at how the initial filters work in Excel and Power BI.

Power Pivot for Excel

In the following Pivot Table example, the highlighted cell has an initial filter of Products[Category] = “Bikes” coming from the rows of the pivot table (shown as 1).  Initial filters can also come from Filters, Columns and Slicers in a pivot table.

Power BI Desktop

Power BI Desktop is similar to Excel, but initial filters can come from almost anywhere in the report.  The highlighted cell below is filtered by Products[Category]=”Bikes” (shown as 1) just like in Excel above, but there is also a cross filter coming from Territory[Country]=”Australia” (shown as 2).  Both of these filters are part of the initial filters.  Filters can also come from Columns, Slicers, and the filter section on the right hand side of the Power BI report window.

Now onto a new topic.

Row By Row Evaluation

A second important topic for you to understand is ‘row by row evaluation’.  Not every DAX formula is capable of doing calculations row by row.  Sometimes DAX has the ability to be evaluated row by row (such as in a calculated column) and sometimes it cannot (as in a regular measure). [As an aside, this is single handily the most difficult concept to learn in the DAX language and it is covered in my various Power BI courses.] You can write a single formula in a calculated column in the Sales table, such as Sales[Qty] * Sales[Price Per Unit] and this formula is evaluated one row at a time down the entire column.  If you were to write this exact same formula as a measure, you would get an error.

The technical term for this behaviour is “Row Context”, but I prefer to use the term “Row by Row Evaluation” as it is less intimidating for most people.

SUM() vs SUMX() in a nutshell

• SUM() operates over a single column and has no awareness of individual rows in the column (no row by row evaluation).
• SUMX() can operate on multiple columns in a table and can complete row by row evaluation in those columns.

Both functions can end up giving you the same result (maybe, maybe not), but they come up with the answer in a very different way (*more on that later).  Both SUM() and SUMX() often give the same results inside the rows of a matrix or visual, but often give different results in the sub totals and totals section of a visual.

The SUM() Function

Syntax: = SUM(<Column Name>)

Example: Total Sales = SUM(Sales[ExtendedAmount])

The SUM() function operates over a single column of data to aggregate all the data in that single column with the current filters applied – filter first, evaluate second.

The SUMX() Function

Syntax: = SUMX(<Table>, <expression> )

Example: Total Sales SUMX = SUMX(Sales, Sales[Qty] * Sales[Price Per Unit])

SUMX() will iterate through a table specified in the first parameter, one row at a time, and complete a calculation specified in the second parameter, eg Quantity x Price Per Unit as shown in the example above with the current filters applied (i.e. still filter first, evaluate second).  Once it has done this for every row in the specified table (after the current filters are applied), it then adds up the total of all of the row by row calculations to get the total.   It is this total that is returned as the result.

SUM() vs SUMX(): Which One Should I Use?

Whether to use SUM or SUMX  in DAX really depends on your personal preference and the structure of your data.  Let’s look at a couple of examples.

1. Quantity and Price Per Unit
2. Extended Amount
3. Totals Don’t Add Up

1. Quantity and Price

If your Sales table contains a column for Quantity and another column for “Price Per Unit” (as shown above), then you will necessarily need to multiply (one row at a time) the Quantity by the “price per unit” in order to get Total Sales.  It is no good adding up the total quantity SUM(Quantity) and multiplying it by the average price AVERAGE(Price Per Unit) as this will give the wrong answer.

If your data is structured in this way (like the image above), then you simply must* use SUMX()  – this is what the iterator functions were designed to do.  Here is what the formula would look like.

Total Sales 1 =SUMX(Sales,Sales[Qty] * Sales[Price Per Unit])

You can always spot an Iterator function as it always has a table as the first input parameter.  This is the table that is iterated over by the function.

*Note:  I say must, but actually this is where many (most) business people tend to make  a mistake.  To solve this problem, rather than using SUMX() as prescribed above, many business people tend to gravitate towards a calculated column to solve the problem.  A calculated column solves the problem in the same way as SUMX(), but with one big difference – it permanently stores the row by row results in in a new column in the table.  This is generally bad and you should avoid this.  I recommend you read my article Measures vs Calculated Columns for a more in depth coverage of this topic.

2. Extended Amount

If your data contains a single column with the Extended Total Sales for that line item (ie it doesn’t have quantity and price per unit), then you can use either SUM() or SUMX() to add up the values.

Total Sales 2 =SUM(Sales[Total Sales])

Total Sales 2 alternate = SUMX(Sales, Sales[Total Sales])

Syntax Sugar

Despite what your intuition may tell you, this alternate formula using SUMX() is identical in performance and efficiency to the SUM() version.  In fact, they are technically the same formula.  SUM(Table[Column]) is just syntax sugar for SUMX(Table,Table[Column]).  Syntax sugar refers to a simplified way of writing a formula that is easier to understand and easier to write, but under the hood they are identical. Note, the simplified version using SUM can only operate over a single column. If you want to operate over multiple columns, you must use the full SUMX syntax.

3. Totals Don’t Add Up

There is another use case when you simply must use SUMX() that is less obvious. When you encounter the problem where the totals don’t add up as you need/expect, you will need to use an iterator like SUMX to get the correct total.  I have created a small table of sample data to explain.

The table above shows 4 customers with the average amount of money they spend each time they have shopped as well as the number of times they have been shopping. If I load this data into Power BI and then try to use aggregator functions to find the average spend across all customers as well as the total amount spent, I get the wrong answers in the total row (as shown below).

Here are the measures from above.

Total Number of Visits = SUM(VisitData[Number of Visits]) – the total is correct for this formula.

Avg Spent per visit Wrong= AVERAGE(VisitData[Spend per Visit]) – the total is wrong here.

Total Spent Wrong = [Avg Spent per visit Wrong] * [Total Number of Visits] – the total is wrong here too.

The first measure [Total Number of Visits] is correct because the data is additive, but the other 2 measures give the wrong result on the total row despite giving the correct result on the rows within the table. This is a classic situation where you can’t perform multiplication on the averages at the grand total level. Given the sample data that I started with, the only way to calculate the correct answer is to complete a row by row evaluation for each customer in the table as shown below.

In this second table above I have written a SUMX() to create the Total Spent SUMX (row by row) first. Only then do I calculate the average spend per visit as the final formula.

Total Number of Visits = SUM(VisitData[Number of Visits]) Total Spent SUMX = SUMX(VisitData,VisitData[Spend per Visit] * VisitData[Number of Visits])

Avg Spent per visit Correct = DIVIDE([Total Spent SUMX] , [Total Number of Visits])

In this second case, SUMX is working through the table of data one row at a time and is correctly calculating the result, even for the total row at the bottom of the table.

Final Thoughts on SUM() vs SUMX()

As always, there are exceptions to the rules.  Everyone’s data is different, so test out the techniques shown when comparing SUM() vs SUMX() on your own data and see what gives you the best results.  If your data models are small and fast then it probably doesn’t matter.  If your data models start to get large and slow, then it is time to investigate the best options to try to maximise performance.

Want to Learn More from a Pro?

If you found it easy to learn from this article, then you may like to consider completing some more structured learning from me.  You can learn more from me in different ways, including:

These invaluable tools are sure to assist you and all have the same “easy to learn and easy to understand” approach.