In Power BI, SUM and SUMX functions have specific differences and applications. SUM performs a total sum of values in a column, while SUMX performs row-by-row calculations and sums the results. SUM is recommended for adding values from a single column, while SUMX is more flexible and allows you to perform operations on each row. Additionally, SUM can be considered a simplified way of writing SUMX (more on this later). Next, we will explore their applications and the relationship between both functions, see when to use SUM vs SUMX.
Differences between SUM and SUMX in Power BI
In the context of Power BI, SUM and SUMX functions belong to different calculation engines. While SUM uses the aggregator engine, SUMX uses the iterator engine. What does this mean?
The aggregator engine is responsible for performing aggregation calculations on a dataset, such as the total sum of the values in a column. On the other hand, the iterator engine is responsible for performing row-by-row calculations, iterating each row of a table, and applying expressions and operations to each of them.
SUM: total sum of values in a column (SUM vs SUMX)
The SUM function is used to calculate the total sum of all values in a column of data in Power BI. Its operation is simple: it takes all the values of the specified column and adds them, returning the result in a single row. Unlike SUMX, it does not perform row-by-row calculations.
This function is particularly useful when you need to quickly calculate the sum of a single column without having to perform additional operations on each row. For example, if we want to obtain the total sum of the amount of all the products present in a table, we can use the SUM function and efficiently obtain the desired result.
SUMX: Line-by-line calculations and adding results (SUMX vs SUM)
In contrast, the SUMX function is used to perform row-by-row calculations in Power BI. This means that it iterates over each row of a specified table and performs operations and expressions on each of them. Next, add the results obtained row by row to obtain an aggregate result.
SUMX is a more flexible and sophisticated function that allows you to perform more complex calculations on each row before adding the results. For example, if we want to calculate the sum of the amount only for the “Drinks” category, we can use SUMX to scroll through each row, apply a condition that filters only the rows of the “Drinks” category, and then sum the amount values corresponding to those rows .
Additionally, SUMX is also useful when you need to use more specific expressions and operations to perform calculations on each row, such as calculating total sales based on a parameterized expression by multiplying Quantity by Price.
Applications of the SUM vs SUMX function
The SUM function in Power BI has several applications that can be leveraged to perform sum calculations on columns of data. Below we will explore two representative examples of using the SUM function: the sum of the quantity of all products and some tips on when to use it.
Sum of the amount of all products
One of the most common cases where the SUM function is used is to calculate the sum of the amount of all products. This application is useful when you want to know the total sales or revenue generated by all products in a data table.
For example, let’s say you have a table that records sales of several products, with a column called “Amount” that contains the value of each sale. If we want to obtain the total sales of all products we can apply the SUM function to the “Amount” column. The result will be the sum of all the values in the column, i.e. the total amount of all sales.
Tips for using SUM
Although the SUM function can be used in different situations, it is important to keep in mind some recommendations for its correct use. Below are some considerations to keep in mind:
- Use SUM when you need to total the values in a column without having to perform additional calculations per row.
- Apply SUM in situations where you are only working with a specific column and do not need to iterate through the rows of a table.
- Consider the simplicity of the syntax and efficiency of SUM compared to other more complex functions, such as SUMX.
By following these tips, you can take full advantage of the SUM function and get accurate and efficient results when calculating sums across columns of data.
Total Sales = SUM(Sales(SalesAmount))
If you have come this far, you might be interested in my book “Impact with Power BI” you can find it HERE or by clicking on the image.
Applications of the SUMX and SUM functions
The SUMX function in Power BI provides more flexibility by allowing row-by-row calculation and the ability to perform operations before summing the results. Below are two key application cases:
Sum of the amount of the ‘Drinks’ category only
Imagine you have a table with product data, where each row contains information about a product, including name, category, and sales amount. If you want to calculate the sum of the amount only for products in the ‘Drinks’ category, in this case the SUMX function is indicated.
Using SUMX, you can scroll through each row of the table and evaluate the category of each product. Only the amounts of products that belong to the “Drinks” category will be added, ignoring the others. This gives you an accurate and specific result for the desired category.
SalesDrinks = SUMX(FILTER(Sales, Product(Category) = ‘Drinks’), Sales(SalesAmount))
Sum of total sales using expressions
Sometimes you may need to perform more complex calculations before adding the results. The SUMX function gives you this functionality using expressions.
For example, suppose you have a table with sales data, where each row represents a single sale with information such as product, quantity sold, and unit price. If you wanted to calculate total sales, you would multiply the quantity sold by the unit price before adding the results. With SUMX you can write an expression that performs this calculation for each row and then add the results, precisely obtaining the sum of the total sales.
Total Sales2 = SUMX(Sales, Sales(Quantity) * Sales(Price))
Relationship between SUM and SUMX
SUM sugar syntax of SUMX
An interesting relationship between SUM and SUMX is that the SUM function can be thought of as a “sugar syntax” of the SUMX function. In other words, SUM is a simplified way of writing SUMX without having to explicitly specify said function with its two parameters.
The SUM function is used when we just want to get the total sum of the values in a column without having to perform additional calculations on each row. For example, if we want to calculate the total sum of the above sales, we can simply use SUM instead of SUMX. By skipping the line-by-line iteration, we get cleaner, more concise code.
It’s important to note that while SUM makes it easier to write code, it also comes with some limitations in terms of flexibility. If we need to perform multiple operations or advanced calculations on each row before adding the results, we will need to use the SUMX function.
TotalSales = SUM(Sales(SalesAmount)) is exactly the same as TotalSales = SUMX(Sales, Sales(SalesAmount))
But didn’t you say before that they also use different calculation engines? Yes, this is also true and when we use SUMX passing a single table and a single column as parameters, as we did in this latest SUMX, here it uses the aggregator engine, because it doesn’t need to iterate through the rows since it doesn’t have to compute any expression previous and you just need to add that column.
What determines whether or not to use the iterator engine is whether an expression exists as the second parameter of SUMX
In other words, SUM vs SUMX are the same in case you use a single table column to add it. You can write the full syntax with SUMX or the simpler sugar syntax with SUM.
I’ll demonstrate it to you in this image using DAX Studio to show the instruction coming internally to the DAX engine so you can see it’s exactly the same:
Conclusions SUM vs SUMX in DAX
The SUM function could not exist and we would get exactly the same result with SUMX, in fact the same instruction is generated internally.
SUM contemplates a subset of the functionalities of SUMX, it is the simplest expression, indicating a single column of a table.
SUMX allows you to perform many calculations that are totally limited with SUM.
Feel free to use SUM or SUMX by quoting a single column from a table. You’re really doing the same thing. SUM is usually used because its syntax is simpler, but it doesn’t matter.
Additional resources on SUM and SUMX in Power BI
If you are interested in learning even more about the differences and applications of SUM and SUMX functions in Power BI, there are a number of additional resources available that can be very helpful. These resources will help you expand your knowledge and master the use of these functions in data analysis.
- How-To Videos: On platforms like YouTube, you can find a wide variety of tutorials and how-to videos about SUM and SUMX in Power BI. Be careful and check that they respect what we have said here about “sugar syntax” and that they do not mislead you by giving the wrong explanation.
- Specialized Articles: There are numerous articles available online that specifically discuss the differences and applications of SUM and SUMX. Personally I recommend this from SQLBI: .
- Online courses and tutorials: confirm every time they talk to you about “sugar syntax” and that they explain it correctly, avoid creating confusion by not adequately explaining these concepts.
Remember that understanding and mastering the SUM and SUMX functions in Power BI is essential to performing effective data analysis and gaining valuable insights. Take advantage of these additional resources to broaden your knowledge and improve your skills in using these features. Stay updated and aware of the latest Power BI developments to make the most of all its features in your data analysis projects.
Leave a Reply