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

You must be logged in to post a comment.