Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# The Difference Between SUM vs SUMX In Power BI

by | 1:00 pm EDT | July 17, 2020 | Power BI

There is still a lot of confusion about the difference between SUM vs SUMX in Power BI. This is key knowledge that users have to master because both functions can be used across different scenarios, but there are cases where one is more efficient than the other. You may watch the full video of this tutorial at the bottom of this blog.

I’m going to focus on one example here that would show the distinction between the two. But before I jump into that example, it is important to understand the difference between an aggregating function and an iterating function.

## Aggregators Vs Iterators

When it comes to DAX, there are two types of calculation engines – the aggregators and iterators.

Aggregating functions include SUM, AVERAGE, MIN, MAX and COUNT. Iterators, on the other hand, are functions that have an X at the end, like SUMX.

Iterating functions go through every single row of a table to add logic to each of these rows.

Aggregating functions look at the entire column left over after the context is placed in a formula. From there, a single aggregation is done for the entire column at a single time.

## SUM As An Aggregator

How is SUM used as an aggregator?

In this example, I’m going to compute for the Total Revenue in the sample data given.

The context is always important here. In this case, each specific date is the context of each specific result.

If I dig deeper into this table, it will show that there is a direct relationship flowing from the Date going into the Sales table.

Then if I look at the data working underneath this model, this is how everything fits together.

So the relationship is linked to the Order Date column here. Once specific dates from this column are filtered, the corresponding results are shown under the Revenue column.

From there, the SUM would just do one big calculation of the filtered results.

## SUMX As An Iterator

Now, I’m going to use SUMX on the same sample data so that you can see the difference. I can actually calculate for that Revenue without touching the Revenue column.

When the SUMX function is used, it will always ask for a table. Note that either a physical table or a virtual table can be used here.

To come up with the Revenue, I’m going to choose the Sales table. Then, I’ll place an expression, which can be a measure or a specific column from that table into this formula so that it can start running logic on every row. The expression, as explained here, returns the sum of an expression evaluated for each row of the table.

Since the sample data includes the Order Quantity, I’m going to use that here to get the Total. I’m also going to use the Unit Price.

Once I drag that formula into the report, the results are exactly the same.

Of course, they’re both showing the same results because they are both deriving data from the same two columns – the Order Quantity and the Unit Price.

Why use the SUMX if it yields the same result as the SUM anyway?

With the SUMX, the logic is applied not just to an entire column, but to every single row within that column. In fact, I could delete the Revenue column and still be able to retrieve specific results.

So imagine that logic being applied at every row. It multiplies the Order Quantity and Unit Price for the 1st row then saves that into the memory. It does the same thing to the 2nd row and all the other rows after that, saving each individual result.

This means that at the end, what’s being used to calculate the SUMX is not the physical data on the table, but the results saved in the memory.

## Conclusion

Hopefully I was able to explain the main difference between SUM vs SUMX in Power BI, especially to those who are still getting the hang of what Power BI can really do.

The rule of thumb is if it’s a simple, straightforward scenario that can be solved by an aggregating function, use SUM. But if more advanced logic is required, use SUMX.

SUMX will also be useful in cases where you have thousands to millions of rows. As long as the tables and columns referenced in your measures are there, using iterating functions would make the process more efficient.

All the best,

Sam

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...

## Power BI Copilot: Enhancing Data Analysis with AI Integration

Are you ready to elevate your data analysis capabilities? Then let's delve into the realm of Power BI...

## What is Microsoft Fabric? A Guide to Features & Benefits

Imagine a one-stop shop that not only crunches numbers like it's eating cereal for breakfast but also...

## Python AttributeError: ‘dict’ object has no attribute Fix

Python is widely appreciated for its readability and ease of understanding. Among its numerous...

## Power BI vs Tableau: Differences Compared 2023

In the world of data visualization and analytics, two industry-leading data visualization tools stand...

## Pandas Drop Index Column: Explained With Examples

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality...

## Microsoft DP-500 Explained (Bonus Cheat Sheet)

Data is the backbone of businesses these days, and having proof that you know how to handle and make...

## Power BI Financial Dashboard Examples: Let’s Get Specific

Power BI financial dashboards provide a quick and easy way to monitor an organization's financial...

## Power BI Themes: User Guide With Examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various...

## Power BI Hierarchy: User Guide With Examples

Imagine being able to look at your data from every which way â€” from the bird's eye view right down to...

## Power BI Calendar Table: What Is it And How to Use It

Time is of the essence in the world of data analysis, and without a proper understanding of when or how...