AVERAGE IF and MAX IF equivalent in Powerpivot DAX?

[Jay Man]

Hi there

I’m exploring Powerpivot DAX formulae and its ability to do the equivalent of array formulae in Excel using dynamic/cell based criteria.

I’d like to do the equivalent of
1. AVERAGEIFS
2. MAXIFs.

I’ve scanned through previous posts, but cant seem to find the syntax that you would you use for the calculated columns in a Powerpivot Data Model?
i.e – what is the syntax when the criteria changes with each row in the table?

Example:

Column1 Column2 AVERAGEIF using Col1 as criteria MAXIF using col 1 as criteria
A 2 1.5 2
B 3 3 3
C 4 4 4
A 1 1.5 2
B 3 3 3
C 4 4 4

Thanks in advance!!

 

[scottsen]

This is one of those awkward situations where I have to admit to not knowing native excel very well...

So, two questions:
* What is the expected output? For rows with an A... the avg of all the A rows? (etc)?
* Does it need to be a calculated column? (vs a meassure / calculated column -- see here When to Use Measures vs. Calc Columns « PowerPivotPro)

 

[Jay Man]

Hi Scott,

Answers in red below....

So, two questions:
* What is the expected output? For rows with an A... the avg of all the A rows? (etc)?

Yes. Sorry the formatting for the table got messed up...but yes I want the average for A appearing in any row with A, the average of B appearing in any row with B etc etc.
* Does it need to be a calculated column? (vs a meassure / calculated column -- see here When to Use Measures vs. Calc Columns « PowerPivotPro)[/QUOTE]

Yes. Calculated column please...values needs to be dynamic with each row ...plus I dont want the result to change when I modify the layout of any downstream pivot table that I use..thanks for article though!

 

[scottsen]

Somewhat easier as a measure but... calc colum, sure, we can do that It just looks a bit scary, and I don't want to turn you off to Power Pivot

AvgCol = CALCULATE(AVERAGE(Table1[Col2]), FILTER(Table1, Table1[Col1] = EARLIER(Table1[Col1]))
MaxCol := CALCULATE(MAX(Table1[Col2]), FILTER(Table1, Table1[Col1] = EARLIER(Table1[Col1])))

 

[Jay Man]

Works like a charm! Thank you very much!

 

[bramkn]

Hi there,

I wonder if u could give me the measure, my situation:
I need to average a number(grade) but only for the grades which have a date later/earlier then an other date.
So There is a Table with all the grades people have(usually they have 2 grades) these have to be averaged but only when the date from the grade is earlier then an other date from a different table.
I have all the tables setup in a Powerpivot model and they are connected properly, but I am new to the formulas.
I hope u understand what my issue is and can help me.

Somewhat easier as a measure but... calc colum, sure, we can do that It just looks a bit scary, and I don't want to turn you off to Power Pivot

AvgCol = CALCULATE(AVERAGE(Table1[Col2]), FILTER(Table1, Table1[Col1] = EARLIER(Table1[Col1]))
MaxCol := CALCULATE(MAX(Table1[Col2]), FILTER(Table1, Table1[Col1] = EARLIER(Table1[Col1])))

 

[scottsen]

Happy to help, but will need you to include a picture of the model, or some sample data or something...

 

[bramkn]

Happy to help, but will need you to include a picture of the model, or some sample data or something...

Ty for your responds and sorry for my late responds.
My data wasn't correct apparently so some other stuff I was trying to do didn't work either. I am fixing my model now, but will need your help again later probably. I will ask you again when I am back on track again and any questions arise.

 

[dmasonring]

Hello scottsen,

I am facing a similar problem. This is my first time using DAX's Calculate. In my original workbook, I have AVERAGEIFS(average_range,criteria_range,criteria,...). I want to create a calculated column of an AVERAGEIFS so I can easily manipulate my data in a powerpivot. I am finding Average Sales using 3 criteria: Markets, Hierarchy, and Climate. I also have 2 different tables. I have not linked them as I get the error message ("too many duplicate records to link"). Is it necessary to link?

Here is what I typed in my Calculated Column:
=CALCULATE(AVERAGE(TABLE 1[(SLS],FILTER('TABLE 2','TABLE 2'[MARKET]=EARLIER('TABLE 2'[MARKET]),FILTER('TABLE 2','TABLE 2'[HIERARCHY]=EARLIER('TABLE 2'[HIERARCHY]),FILTER('TABLE 2','TABLE 2'[CLIMATE]=EARLIER('TABLE 2'[CLIMATE])))

I am getting an error message. I thought I repeated the formula you gave, but added 2 more arguments. Do you think I should try to combine my data into one source since my tables cannot be linked?

Thank you for any help!

 

[gazpage]

You need one filter with the three conditions connected by &&, rather that three different FILTERs.

If you don't link or combine your tables, how can they affect one another?