Problem Statement:
The sales department needs to calculate the total sales by accounting for both the quantity sold and the price per product. However, the sales table contains discounts per item that need to be applied on a row-by-row basis before calculating the total revenue. Simple aggregation functions like SUM cannot achieve this because the calculation requires multiplying multiple columns and applying row-specific conditions.
Why SUMX is the Solution:
- In this scenario, SUMX {Syntax: SUMX(<table>, <expression>)} can handle row-wise calculations like:
- Use Case 1 {simpler use case}: Multiplying Quantity by Price for each row in the table.Calculated tables
- Use Case 2 {complex use case}: Multiplying Quantity by (Price – Discount) for each row in the Sales table contains Quantity & Price with related Discount table contains Discount columns respectively.Calculated columns
- Accurately aggregating these custom-calculated values to get the total revenue.
DAX Example:
If you have columns like Quantity and Price in a Sales table:
DAX:
Use Case 1: Total Sales w/o Discount = SUMX(Sales, Sales[Quantity] * Sales[Price])
This will iterate through each row of the Sales table, apply the discount to the price, multiply the discounted price by the quantity, and finally sum up all the results to give you the total revenue.
Use Case 2: Total Sales with Discount = SUMX(Sales, Sales[Quantity] * (Sales[Price] – RELATED(Discount[Discount])))
Key Point:
SUMX is ideal here because a simple SUM function won’t handle custom calculations or row-wise logic (e.g., subtracting a discount before multiplying). It provides flexibility in defining the exact formula you need to use per row.