SUMX Function in DAX - PowerBI - With Example

DAX (Data Analysis Expression) is a powerful language used to define calculations in Power BI, Excel, and other Microsoft tools. One of the useful functions in DAX is the SUMX function, which allows you to perform a sum across a table, using a formula that you specify.

To understand how the SUMX function works, let's consider a simple example. Suppose you have a table of sales data, with the following columns:

Date	Customer	Product	Quantity	Price
1/1/2022	John	A	2	$10
1/1/2022	Mary	B	3	$20
1/2/2022	John	C	4	$15
1/3/2022	Mary	A	1	$10
1/4/2022	John	B	2	$20

Let's say we want to calculate the total sales for each customer. We can use the SUMX function to do this, by specifying a formula that multiplies the quantity by the price for each row in the table.

Here's the syntax for the SUMX function:

Total Sales =
		SUMX(<table>, <expression>)
		

To use the SUMX function in our example, we would write the following formula:

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

This formula would iterate through each row in the Sales table and calculate the total sales for each customer by multiplying the Quantity by the Price.

The result of this formula would be a table with the following columns:

Customer	Total Sales
John	$120
Mary	$70

We can also use the SUMX function to perform calculations on a filtered table. For example, suppose we want to calculate the total sales for each customer in the month of January. We can use the FILTER function to filter the Sales table by the Date column, and then pass the filtered table to the SUMX function.

Here's the modified formula:

Total Sales =
		SUMX(
		    FILTER(Sales, Sales[Date] >= DATE(2022, 1, 1) && Sales[Date] < DATE(2022, 2, 1)),
		    Sales[Quantity] * Sales[Price]
		    )

This formula would return the following table:

Customer	Total Sales
John	$20
Mary	$60

As you can see, the SUMX function is a powerful tool for performing sums across a table, using a formula that you specify. It's particularly useful when you need to perform calculations on a filtered table, or when you need to use a more complex formula for your calculations.

I hope this article has helped you understand how the SUMX function works in DAX.