Power BI's Sum vs. Sumx

Power BI’s SUM vs SUMX: What is the Difference, REALLY?

The difference between the SUM and SUMX functions is a confusing topic for many Power BI developers. Though they are similar, I can assure you there is a big difference, and if used effectively, it can unlock a whole new layer of potential in your dashboard development and data analyses. In this blog, I will briefly explain the difference between the two and provide a simple example for each.

It’s helpful to take a step back and understand that there are two basic calculation engines in Power BI. There is an aggregator engine and an iterator engine.

SUM is a Member of the Aggregator Engine

SUM is a perfect example of a function that belongs to the aggregator engine. It adds (or aggregates) every value in a single column and returns the result. The only thing SUM is capable of doing is adding all of the values of one column. In other words, it has no visibility into the concept of what a row is and cannot do any kind of row-by-row evaluation (other aggregator functions include COUNT, AVERAGE, MAX, MIN, etc.)

  • The syntax for the SUM function is: SUM(ColumnName)

The dataset in the example below has a Sales column that we’ll use the SUM on, and then display it by the Category dimension – you can slice any measure by any dimension that it shares a relationship with.

The formula to achieve this is: 

Total Sales = SUM(Orders[Sales])

Sales and Category columns in the Orders table
Data view showing the Sales and Category columns in the Orders table.

Note: I always keep my measures in a separate Measure Table (in the screenshot below) – read about that best practice in this Creating a Measures Table in Power BI blog post.

Report view showing Total Sales by Category
Report view showing Total Sales by Category

SUMX is a Member of the Iterator Engine

The fact that SUMX is an iterator function means that it iterates through every row of a specified table to complete the evaluation, and then works out an additional piece of logic within the function itself. Unlike aggregators, functions belonging to the iterator engine have an awareness of rows in the table (other iterator functions include COUNTX, AVERAGEX, RANKX, PRODUCTX, etc), this is how they are able to perform row-by-row calculations.

  • The syntax for the SUMX function is: SUMX(Table, Expression)

Let’s say that we want to use the same dataset to create a measure for what the Total Sales are after any Discounts have been applied. We can use the SUMX function to iterate through each row of the data and apply that logic to create a Sales Less Discounts measure.

The formula to achieve this is:  Sales Less Discounts = SUMX(Orders,Orders[Sales]-Orders[Discount])

Report view showing Total Sales, Discounts, and Sales Less Discounts by Category
Report view showing Total Sales, Discounts, and Sales Less Discounts by Category

Looking at the chart on the top right in the report, we can confirm that the Sales Less Discounts column is correctly subtracting the Discount column from the Total Sales column. Effectively, what the SUMX function is doing here is going through each row of the Sales column and subtracting the Discount column from the same row.

To summarize the difference between SUM and SUMX (no pun intended): 

  • SUM aggregates a single column and produces a single value after applying all filters.
  • SUMX iterates through each row of a table then aggregate the values after: Applying all filters, and then applies an additional piece of logic before producing the final value.
Want to Learn More?

Have any questions, comments, or curiosities? Feel free to reach out to me about Power BI via email gavin.pedersen@tessellationconsulting.com at or on LinkedIn.

About Tessellation

We are a modern analytics consultancy. We enable and manage organizations’ analytics and self-service teams by educating people, optimizing technology, developing world-class products, and providing sustainable results. Curious to know how we can level up your organization’s analytics? Click here!

Interested in joining the Tessellation Team as a data analyst, Tableau expert, dashboard designer, or data scientist? We’re hiring! Check out our latest job listings on our website and on our Linkedin page!

Subscribe to Our 'Data Cap' Newsletter!

Want a monthly insights from the Tessellation Team with tips, tricks, and secrets to improve your analytics?

Sign up below and we'll deliver articles, events, and how to's straight to your inbox.
Comments are closed.