DAX: The difference between SUM and SUMX

Introduction

Understanding the difference between your standard aggregate functions and your X aggregate functions is useful because there will be scenarios for using one over the other.

How does SUM() work

Sum, average, min, max etc are all fairly easy to understand. They accept a single column input and aggregate the data. Here is the syntax from Microsoft Documentation.

SUM([Column Input]

Example:

Revenue = SUM('Sales'[Sales Amount])

How does SUMX() work

SUMX() on the other hand is an iterator. It will iterate through all the rows in a table and evaluate an expression. This is perfect for scenarios where you need to apply some logic before aggregating.

SUMX requires two inputs, a table and an expression.

SUMX('Table', <Expression>)

Lets say we want to know the revenue but we don’t have a [Revenue] or [Sale Amount] column. Instead we need to calculate revenue from the number of sales and the price of an item. In this instance SUMX() is just what we need.

Revenue = SUMX('Sales', [Units] * [Unit Price])

It’s good to remember though that as SUMX() is an iterator this can be a costly function to process over large tables. If this becomes an issue then you best look for more advanced ways of computing the same.

SUMX() with FILTER()

Because SUMX() accepts a table input you can also take advantage of providing filtered datasets. Lets say I want a column for calculating how much revenue we’ve generated from bulk sales. In this example I consider any sale where the quantity is greater than 50 to be a bulk sale.

Bulk Sale Revenue = 
     SUMX( 
         FILTER('Sales', [Units] > 25 ),
         [Units] * [Unit Price]
         )

Using SUMX() work out the % of revenue

We could also use the ALL() function in order to ignore row context and return a total of revenue at the row level. This enables us to then calculate the relative percentage of revenue.

Total Revenue = 
     SUMX( 
         ALL('Sales'),
         [Units] * [Unit Price]
         )

Using my new measure [Total Revenue] I can then create the following…

% of Revenue = DIVIDE([Revenue],[Total Revenue])

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.