Knowing different functions in Power BI and the differences between them is a handy way of becoming an expert in Power BI. Data Analysis Expression or more commonly known as DAX allows us to create calculations in Power BI easily through an array of built-in functions. 

Today I wanna talk about two such functions which are very commonly used: SUM vs SUMX functions. Understanding the differences between these functions can not only clarify the differences between these two but also open our eyes to some other functions that can help take our Power BI and DAX skills to the next level quite easily. 

How? Let’s walk through the differences between Sum and Sumx together and that should help clarify why I say so. 

What are Power BI’s SUM and SUMX?

I am sure most of you already know how a Sum function works. But to understand the difference between the two, it is important to know exactly how a Sum function operates from within. 

Sum Function Explained

The SUM function is straightforward and easy to understand. It is used to add up all the values in a single column. SUM works on a columnar level, meaning it aggregates the values from an entire column and returns a single value.

Syntax: 

SUM(column_name)

Take this dataset for instance where we have the customer wise scores including health score and CSM sentiment. 

Here if we want to add up the Health Score, we can just use the following DAX function: 

Total Health Score = sum(score[Health Score])

Now we can use this measure in the visuals as follows:

This Sum function aggregates or adds up all the values in the Health Score column and returns the total. 

Sumx Function Explained

SUMX, on the other hand, is more complex and flexible. It is an iterator function that evaluates an expression for each row in a table and then sums up the results. SUMX works row by row, allowing for more dynamic calculations based on the context of each row.

This might sound a bit complicated but let me break it down.
Let’s say, from the example above, we want to calculate the total sum of the difference between the health score and CSM sentiment known as the Net Score. To do this, generally, we would calculate two different functions where we would calculate the sum of health score and the sum of CSM sentiment and then subtract the two measures. 

However, thanks to the SUMX function, we don’t have to take this long elaborate method to calculate this. 

The SUMX function follows this syntax:

SUMX(table, expression)

Hence, the DAX function to calculate the Net Score would look like this:

Net Score = sumx(score,score[Health Score]-score[CSM Sentiment])

Here the Sumx function evaluates the calculation row by row by subtracting the CSM sentiment from the Health Score for each individual row and then summing these values together at the end.

This is best explained using a tabular chart as shown below. Here the net score is being calculated at each individual row. 

The Sumx function does the same thing without the need for an additional column in the dataset. It stores this row level result in a temporary memory which is then added up to return the final Net Score summation. 

Best Practices While Using The SUMX Function

Avoid Overusing SUMX

While SUMX is powerful, it should be used carefully. Overusing SUMX, especially on large datasets, can lead to performance issues. Always evaluate if SUM can achieve the desired result before opting for SUMX.

In theory, we can use SUMX for a simple aggregation to get the summation of a column; however, due to the iterative nature of SUMX, it uses more resources than a simple SUM function. This will lead to performance issues in your dashboard with longer response times and higher memory usage.

Tips To Optimize Performance While Using SUMX

When using SUMX, consider optimizing performance by:

• Filtering data to reduce the number of rows being evaluated

• Using variables to store intermediate results and avoid redundant calculations

How Does This Help While Using Other DAX Functions?

You might be wondering, how does knowing this help us in using other DAX Functions? Well, Sumx is not the only iterative function that DAX uses. 

There are other aggregate functions in Power BI which we commonly use such as COUNT, AVERAGE, MAX, MIN, etc. These functions also have their iterative versions which can come in very handy while performing row level calculations.
These include functions such as COUNTX, AVERAGEX, MAXX, MINX RANKX, PRODUCTX, etc.

So now that you know the difference between an aggregator and iterative function, you can use this knowledge to explore further and use these other functions accordingly in your daily reports as well. 

Conclusion

In Power BI, SUM and SUMX are essential functions for data aggregation, each with its specific use cases and advantages. SUM is ideal for simple, column-level aggregations, providing quick and efficient results. SUMX, with its ability to evaluate expressions row by row, offers greater flexibility for more complex calculations. Understanding when and how to use these functions can significantly enhance your data analysis capabilities in Power BI.

I hope this helps you understand how you can utilize these DAX functions in your next report on Power BI. This can be a great way to understand how DAX functions work and can be the portal you need to go deeper into DAX functions and its capabilities which can make your lives easier! 

To know more about DAX functions, visit Case When Blog site to read up on related topics. Here is one which talks about the Calculate function and its nuances. 

FAQs

What is DAX?

Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI. Some examples of DAX are the Sum, Sumx, Calculate functions. 

How to use Calculate Function in Power BI?

The Calculate function in Power BI has main parts to it: the expression and the filter. The expression allows us to place different aggregations such as Sum, AVG, Count etc. The filter allows us to define the column on which we are filtering the data and the filter value. 

The DAX syntax for the calculate function in Power BI is as follows: 

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

It is possible to add multiple filters to the filter section in the function by separating via commas.