Power BI has two fundamental computation engines will help you move forward. An iterator engine and an aggregator engine both exist. The distinction between SUM and SUMX in Power BI is still very unclear. Both functions can be utilized in various contexts, but there are some situations when one is more effective than the other, thus users need to be aware of this crucial information.
What is SUM?
The SUM function in DAX is used to calculate the sum of a column or expression. It is commonly used to aggregate numerical values, such as sales amounts or quantities. The syntax for the SUM function is:
SUM(column_or_expression)For example, let’s say we have a table called “Sales” with columns “Product” and “Quantity”. To calculate the total quantity of all products sold, we can use the SUM function like this:
SUM(Sales[Quantity])This will give us the sum of the “Quantity” column in the “Sales” table.
What is SUMX?
The SUMX function in DAX is used to calculate the sum of an expression evaluated for each row in a table. It is commonly used when we need to perform calculations on a row-by-row basis. The syntax for the SUMX function is:
SUMX(table, expression)For example, let’s say we have a table called “Sales” with columns “Product”, “Quantity”, and “Price”. To calculate the total sales amount for each product, we can use the SUMX function like this:
SUMX(Sales, Sales[Quantity] * Sales[Price])This will give us the sum of the product of the “Quantity” and “Price” columns for each row in the “Sales” table.
Examples:
Let’s look at a few examples to better understand how SUM and SUMX work in Power BI.
Example 1: Total Sales Amount
Suppose we have a table called “Sales” with columns “Product”, “Quantity”, and “Price”. To calculate the total sales amount, we can use the SUM function like this:
SUM(Sales[Quantity] * Sales[Price])This will give us the sum of the product of the “Quantity” and “Price” columns in the “Sales” table, resulting in the total sales amount.
Example 2: Average Sales per Product
Suppose we have a table called “Sales” with columns “Product”, “Quantity”, and “Price”. To calculate the average sales amount per product, we can use the SUMX function like this:
SUMX(SUMMARIZE(Sales, Sales[Product]), SUM(Sales[Quantity] * Sales[Price]))This will give us the sum of the product of the “Quantity” and “Price” columns for each product in the “Sales” table, resulting in the average sales amount per product.
Example 3: Total Revenue by Region
Suppose we have a table called “Sales” with columns “Region”, “Quantity”, and “Price”. To calculate the total revenue by region, we can use the SUMX function like this:
SUMX(SUMMARIZE(Sales, Sales[Region]), SUM(Sales[Quantity] * Sales[Price]))This will give us the sum of the product of the “Quantity” and “Price” columns for each region in the “Sales” table, resulting in the total revenue by region.
Conclusion
The SUM and SUMX functions are powerful tools in Power BI for performing calculations and aggregating data. Understanding how to use these functions correctly can help you analyze and visualize your data effectively. By using these functions in combination with other DAX functions, you can create complex calculations and gain valuable insights from your data.