In this post we cover the difference between the DAX Formula SUM and SUMX. If you are even somewhat familiar with EXCEL you already know what SUM does. SUM aggregates the values over a column taking all rows into account. SUMX performs the same aggregate operation but over a subset of the data.
Basically when you don’t want to SUM every row in the table you use SUMX. The below screenshot shows the syntax.
In the below formula we use SUMX to add up the Quantity of orders from the Sales table where the Quantity is >100
ExSUMX = SUMX(FILTER('Fact Sale','Fact Sale'[Quantity]>100),'Fact Sale'[Quantity])Take a close look at the screenshot below
Notice how the values for the EXSUM(example SUM) is different from EXSUMX( example for SUMX). The table indicates that in total 17359 units were sold in the City Abbotsburg of which 8035 units were from order where the Sale Quantity was greater than 100.
The same behavior can be achieved using other methods as well e.g. using CALCULATE. Which we will discuss in the next post.
The SQL equivalent of the above common would be similar to the query below , the output of which is subsequently joined to other tables based on Order Key etc.
SELECT [order key]
,sum(Quantity)
FROM fact.[order]
WHERE Quantity > 100
GROUP BY [order key]
Please Consider Subscribing
