Forum

What is the SUMX fu...
 
Share:
Notifications
Clear all

What is the SUMX function in DAX, and how does it differ from the regular SUM function in Power BI?


Posts: 53
Guest
Topic starter
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

The SUMX function in DAX is an iterator function, which means it calculates each row individually before summing up the results. Unlike the regular SUM function, which simply adds up values in a single column, SUMX allows you to perform more complex calculations on a row-by-row basis, making it extremely versatile.

Syntax:

dax
 
SUMX(<Table>, <Expression>)
  • Table: The table you want to iterate over (e.g., Sales).
  • Expression: The calculation to be performed on each row before summing the results (e.g., Sales[Quantity] * Sales[Price]).

Example 1: Calculate Total Revenue from Quantity and Price

Let’s say you have a Sales table with Quantity and UnitPrice columns, and you want to calculate the total revenue. Using SUMX, you can multiply Quantity by UnitPrice for each row and then sum up the results:

dax
 
Total_Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Here’s what happens:

  1. SUMX goes through each row in the Sales table.
  2. For each row, it calculates Sales[Quantity] * Sales[UnitPrice].
  3. Finally, it sums up all these row-level results to return the total revenue.

Example 2: Calculate Discounted Total Sales

Imagine you have a Discount column in the Sales table, where each discount is a percentage (e.g., 0.1 for 10%). To calculate the total sales amount after applying these discounts on a row-by-row basis, you can use SUMX:

dax
 
Discounted_Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[Discount]))

This formula first calculates the discounted amount for each sale, then sums up the results. SUMX is essential here because it handles row-by-row calculations based on individual discounts.

Why and When to Use SUMX?

  • Row-Level Calculations: SUMX is perfect for situations where you need to perform calculations on each row before aggregating, like multiplying quantities and prices or applying specific row-based discounts.
  • Flexibility Over SUM: While SUM is straightforward, it can only add values in one column. SUMX is more versatile, as it allows you to define a custom expression for each row.
  • Complex Aggregations: Use SUMX for more advanced calculations, such as conditional or multi-column aggregations.

Key Tip: SUMX can be slower on large datasets since it iterates row by row, so use it when you need that row-level control. For simple sums, SUM is faster and more efficient.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: