Wednesday, February 21, 2024

Power BI - What is difference between SUM & SUMX in DAX programming

In DAX (Data Analysis Expressions), SUM and SUMX are both functions used to aggregate data, but they serve different purposes:

1. SUM Function:
   - The SUM function is a basic aggregation function that calculates the sum of a column or expression for all the rows in a table or a table expression.
   - It works on a column or a single-column table and returns a single scalar value, which is the sum of all the values in the specified column or expression.
   - SUM can be used for simple aggregations where you want to calculate the total of a column without any additional context.
   - Example:
    TotalSales = SUM(Sales[Amount])

   - This calculates the total sales amount by summing the values in the "Amount" column of the "Sales" table.

2. SUMX Function:
   - The SUMX function is an iterator function that applies an expression to each row of a table or table expression and then sums up the results.
   - It allows you to perform calculations row by row and then aggregate the results, providing more flexibility and the ability to apply calculations within a context.
   - SUMX is commonly used when you need to calculate a measure based on filtered or modified data, such as applying calculations to each row and then summing the results.
   - Example:
     TotalSales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) 

   - This calculates the total sales amount by multiplying the "Quantity" column with the "UnitPrice" column for each row in the "Sales" table and then summing up the results.

In summary, while both SUM and SUMX functions are used for aggregation, SUM is used for simple aggregations on a column or a single-column table, whereas SUMX is used for more complex calculations involving iterations over rows and applying expressions within a context.