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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shaykoooo
Helper I
Helper I

Averagex with a Criterion

Hi,
Is it possible to nest a CALCULATE function in an AVERAGEX to create an equivalent to the Excel AVERAGEIF function? And also, the expression argument of the CALCULATE function, does it have to be a measure created already in DAX or that something like Average(Fieldname) will do?

TY
1 ACCEPTED SOLUTION

About point 2. It will depend on if you are using related dimension table column to filter data (or using more than 2 columns for criteria).

If not, you don't need Filter().

 

Though, in the background, it's being converted to Filter(ALL(table),Condition).

 

As well, when you use two different columns as condition, you'll need to write explicit expression using Filter() rather than relying on automatic conversion in the background.

 

Basically, I make it a habit to use FILTER(), so that I don't stumble with more complex conditions (i.e. all my Calculate() construct has same base syntax).

 

See link for more detailed explanation of filter argument in Calculate().

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

View solution in original post

3 REPLIES 3
Chihiro
Solution Sage
Solution Sage

You can use following construct.

 

AvgIf = Calculate(Average(Table[Column]),Filter(Table,[CriteriaColumn]="Criteria"))

1. Good, so no need for an iterative function.
2. If I choose a boolean criteria, I don't have to use FILTER?

About point 2. It will depend on if you are using related dimension table column to filter data (or using more than 2 columns for criteria).

If not, you don't need Filter().

 

Though, in the background, it's being converted to Filter(ALL(table),Condition).

 

As well, when you use two different columns as condition, you'll need to write explicit expression using Filter() rather than relying on automatic conversion in the background.

 

Basically, I make it a habit to use FILTER(), so that I don't stumble with more complex conditions (i.e. all my Calculate() construct has same base syntax).

 

See link for more detailed explanation of filter argument in Calculate().

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.