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.
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.
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])