Power BI: Learn To Use Simple Sum And Expressive SUMX

In Power BI, both SUM and SUMX perform calculations on your data, but they do so in different ways and serve distinct purposes. This tutorial will guide you through the differences, helping you choose the right tool for your analysis needs.

Step 1: Understanding the Basics

• SUM: This function simply adds up all the values in a single specified column. It’s efficient for straightforward summation of existing data points.
• SUMX: This function is more versatile, but also more complex. It iterates through each row of a table, performs a calculation on each row, and then sums the results from all rows.

Step 2: When to Use SUM:

• Simple summation: When you have a single column containing values you want to add up (e.g., total sales, total units sold).
• Performance: SUM is generally faster than SUMX, especially for large datasets.

Step 3: When to Use SUMX:

• Combining multiple columns: When your calculation involves values from multiple columns within a single row (e.g., calculating total revenue by multiplying price and quantity for each product).
• Filtering within the calculation: You can use SUMX to apply filters within the calculation itself, focusing on specific rows or subsets (e.g., calculating average discount only for sales above a certain threshold).
• Dynamic calculations: SUMX can reference other measures or variables within its calculation, enabling more complex scenarios.

Hint: You can think of the x in sumx as standing for expression.

Step 4: Example Scenarios:

Scenario 1: Total Sales

• Use SUM if you have a “Sales Amount” column and simply want the total.
• Formula: SUM('YourTable'[Sales Amount])

Scenario 2: Total Revenue

• Use SUMX if you need to multiply “Price” and “Quantity” for each product and then sum the results.
• Formula: SUMX('YourTable', 'YourTable'[Price] * 'YourTable'[Quantity])

Scenario 3: Average Discount for High-Value Sales

• Use SUMX to filter for sales above a threshold and then calculate the average discount.
• Formula: SUMX(FILTER('YourTable', 'YourTable'[Sales Amount] > 1000), 'YourTable'[Discount] / 'YourTable'[Sales Amount])