Power BI Intermediate Level: 54 - Understanding X-Functions like SUMX, AVERAGEX, etc.

Table of Contents | Power BI Report File | Sample Input

Short summary: The X-Functions act as if they dynamically create a temporary calculated column just for the duration of the calculation. Then the calculation is performed on the temporary calculated column. Unlike actual calculated columns, the content is dynamic, so it can depend on user interactions like filtering.

Overview

The X-functions are often a confusing topic for Power BI beginners and intermediate users. When should you use SUM and when should you use SUMX? The same goes for the other X-functions. Let me give you some simple pointers:

• Use SUM when summing over an existing column is enough
• Use SUMX when you want to (temporarily) create a new calculated column to sum over
• Also use SUMX if you want the content of the temporary calculated column to be dynamic.

There are also performance implications. If you create (permanent) calculated columns for later use with SUM or other functions, the calculated columns increase the model size but the calculation will be quick. On the other hand, when using X-functions such as SUMX, the calculations will take longer but the model size will not increase. However, in practice this is only relevant if you face performance issues.

Using Calculated Column and SUM with Static Content

Let's look at an example of our transactions table where for now we want to calculated the profit based on a fixed 10% discount. Thereby the profit per transaction is independent of user interaction on the report, i.e. it is static. We can first create a calculated column to get the profit per transaction. For convenience, let's quickly do that in DAX.

Profit after 10% discount = [Sales Amount Before Discount] * (1 - 0.1) - [Menu Item Cost]        

Then we just do our summarization.

54 Sum of Profit after 10% Discount = SUM('46_FACT_Transactions'[Profit after 10% Discount])        

Using SUMX with Static Content

Next, let us assume that we don’t want to create an extra calculated column. One reason for that may be that we don’t need the Profit after 10% Discount column for anything else and don’t want to unnecessarily increase the size of our data model. In that case, we can use a SUMX, which works as if we temporarily created a calculated column just for the execution of the measure. An X-function such as SUMX iterates over a table, and that table needs to be the first argument. For the iteration, you have a row context, just like for calculated columns. So for the second argument, enter the calculation like you would for a calculated column. We use:

54 SUMX of Profit after 10% Discount = 
SUMX(
    '46_FACT_Transactions',
    [Sales Amount Before Discount] * (1 - 0.1) - [Menu Item Cost]
)        

Now this measure does not depend on the calculated column we created earlier but rather calculated the result dynamically as if creating a temporary calculated column just for the calculation. Both SUM and SUMX measures yield the same results for static content.

Using SUMX with Dynamic Content

Let's now allow the user to dynamically vary the discount. For that, let us reuse the parameter table, slicer, and measure from article 51. Just copy and paste the discounts slicer without syncing the data between pages. We can access the value using the measure 51_DIM_Discounts Value.

54 SUMX of Profit after User-Specified Discount = 
SUMX(
    '46_FACT_Transactions',
    [Sales Amount Before Discount] * (1 - [51_DIM_Discounts Value]) - [Menu Item Cost]
)        

Note that this would not work using a calculated column in Power Query or DAX, because those are static with regard to user interaction. On the other hand, SUMX is used in a measure, and measures are calculated dynamically.

Conclusions

As you have seen, with static content you can decide whether you want to create calculated columns and then use SUM or whether you want to do the calculation with SUMX using temporary calculated columns. With dynamic content, i.e. content which depends on user interaction such as a user selecting a discount, you have to use SUMX because only measures are fully dynamic, not calculated columns.

If you don’t have performance issues, I recommend to start out by creating calculated columns and using SUM, AVERAGE, etc. on it. As you become more used to that, you can transition to using SUMX, AVERAGEX, etc. especially if you wouldn’t need the calculated columns except for the measure.

In the next article, I will show you how you can use much of Power BI's DAX functionality like measures and table relationships in Excel.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: Using Power BI Functionality in Excel