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