Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

EnterpriseDNA

Iterating Functions - SUMX & AVERAGEX, Why You Need To Understand These Well

If you are creating some analysis inside of Power BI and you’re utilizing DAX measures, then having a superior understanding of how ITERATING functions work is very important.

 

This is what this particular post will showcase to you - understanding how important these functions are.


To be clear when I mentioned iterating functions, I'm referring to the functions that have an X at the end.

 

The main difference between these and the alternative aggregating functions is that iterating functions evaluate logic at every single row of a table that you place inside the function. 

 

These functions can be much more powerful than a simple aggregation function like SUM or AVERAGE. Mainly because you can implement some very advanced logic inside of these that run evaluations you specify at each and every single row. 

 
What I want to focus on today is the two main iterating functions that I believe are the best. They include SUMX and AVERAGEX.

 

In this first tutorial, I run through AVERAGEX.

 

I run through in detail what you need to understand about the type of tables you can place inside this function. Not only can you put a physical table, but you can also place in these virtual tables (which by itself is a fascinating topic).


Here is where you can get really advanced inside of Power BI. What I mean here is you can manipulate virtual tables inside of iterating functions so you’re only iterating through a certain subset or consolidated version of your data. 

 

I cover this in detail and it’s really well worth understanding how this works if you're not totally across it already. 

 

 

 

In this next unique example, I’m going to show you how you can utilize SUMX within your other formulas. 

 

This is not a simple tutorial but I chose this particular one because I thought that if you can see and understand how this works then you will truly know how iterating functions work inside of Power BI. 

 

 

I decided to cover relatively short and sweet topics in this post mainly because this deserves some true focus on some of the most important DAX functions that you can learn and utilize inside your Power BI reports and models.

 

Enjoy!

 

Sam

 

**** Learning Power BI? ****

FREE COURSE - Ultimate Beginners Guide To Power BI

FREE COURSE - Ultimate Beginners Guide To DAX

FREE - 60 Page DAX Reference Guide Download

FREE - Power BI Resources

Enterprise DNA Membership

Enterprise DNA Online

 

 

 

 

 

Comments

Is the formula for [Average Sales] correct? I see that you're using a variable to define AvgSales at the beginning of the formula. For the grand total, this variable is evaluated with no filter context, so it will return the same value as the [Average Results] measure grand total (2356.01). When the variable is later used in the SUMX, it is not re-evaluated in the context of each month. Rather, it just returns the value 2356.01 for each month. In essence, the SUMX in this formula is just multiplying the variable value by the number of months, right?

Hi All-

I'm looking to combine all DAX Filters in single varaibles. Is there any way?

VAR __ProductFilter =
FILTER(KEEPFILTERS(VALUES('DimProduct'[ProductId])), 'DimProduct'[ProductId] = "ABCD")
VAR __DeviceTypeFilter =
FILTER(KEEPFILTERS(VALUES('DimDeviceType'[DimDeviceType])), 'DimDeviceType'[DimDeviceType] = "PC")
VAR __MarketFilter =
FILTER(KEEPFILTERS(VALUES('DimMarket'[MarketCode])), 'DimMarket'[MarketCode] = "US")
VAR __DateFilter =
FILTER(
KEEPFILTERS(VALUES('DimDate'[CalendarDate])),
AND(
'DimDate'[CalendarDate] >= DATE(2018, 5, 1),
'DimDate'[CalendarDate] < DATE(2019, 5, 14)
)
)

 

-- I want to combine like this but it's not working
VAR __Filters =
    FILTER(
      KEEPFILTERS(VALUES('DimDate'[CalendarDate])),
      AND(
        'DimDate'[CalendarDate] >= DATE(2018, 5, 1),
        'DimDate'[CalendarDate] < DATE(2019, 5, 14)
      ),
      OR (KEEPFILTERS(VALUES('DimMarket'[MarketCode])), 'DimMarket'[MarketCode] = "US")
    )

 

Thanks,

Abhiram