Daily DAX : Day 91 SUMX

 SUMX in Power BI DAX:

Explanation:

SUMX is an iterator function in DAX (Data Analysis Expressions) used in Power BI and other Microsoft BI tools. Here's how it works:

    Syntax: SUMX(table, expression)

    Functionality: 

        SUMX takes two arguments:

            A table or an expression that returns a table.

            An expression to evaluate for each row of that table.

        For each row in the specified table, SUMX evaluates the expression and then sums up all the results. This is particularly useful when you need to perform calculations that require iteration over each row before summing, unlike the simpler SUM function which directly adds up the values of a column.

Main Use Case:

    Weighted Averages or Totals: When you need to calculate totals that depend on each row individually. For example:

        Calculating total sales where each sale has a different tax rate or discount applied. Here, SUMX can compute the net sales for each transaction before summing them up.

        Example: If you have a table Sales with columns Quantity and Price, and you want to calculate the total revenue considering each sale might have different discounts:

        DAX

    TotalRevenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

    This formula would multiply Quantity by Price for each row and then sum those products.

Conditional Sums: When you want to sum values based on conditions that vary per row:

    Suppose you want to sum sales only for products that meet certain criteria:

    DAX

    ConditionalSales = SUMX(FILTER(Sales, Sales[Category] = "Electronics"), Sales[Amount])

    Here, SUMX iterates over only the rows where the category is "Electronics" and sums up the Amount.

Complex Calculations: When you need to perform multiple operations per row before summing, like calculating interest on different payments:

DAX

    TotalInterest = SUMX(Payments, Payments[Amount] * Payments[Rate] * DATEDIFF(Payments[Date], Payments[StartDate], DAY) / 365)

In Summary: SUMX is crucial when you need to apply a calculation to each row of a table before aggregating the result, providing much more flexibility than SUM for scenarios requiring per-row operations.

https://dax.guide/sumx/