Forum Discussion
Solved
Multiple IF conditions with averageX
I currently have the following IF statement to calculate the avg of the previous day's data if greater than 0.4. However, now I need to add a high limit of 2. So really I want it to take the avg of all values between 0.4 and 2. However, I am getting an error messge that "Too many arguments were passed to the AVERAGEX function. The maximum argument count for the function is 2."
Here is my formula with just the lower limit.
AVG CH7 = AVERAGEX(FILTER('Kw/ton','Kw/ton'[Date]=EARLIER('Kw/ton'[Date])),if('Kw/ton'[Chiller7_KW_Ton_value (kW)]<0.4,BLANK(),'Kw/ton'[Chiller7_KW_Ton_value (kW)]))
Here is it with trying to implement the high limit.
AVG CH6 = AVERAGEX(FILTER('Kw/ton','Kw/ton'[Date]=EARLIER('Kw/ton'[Date])),if('Kw/ton'[Chiller6_KW_Ton_value (kW)]<0.4,BLANK(),'Kw/ton'[Chiller6_KW_Ton_value (kW)]),if('Kw/ton'[Chiller6_KW_Ton_value (kW)]>2,BLANK(),'Kw/ton'[Chiller6_KW_Ton_value (kW)]))
Any help would be greatly appreciated!
You cannot use if statement as the expression. To get the average between of all values between 0.4 and 2, try add the conditions in the filters:
CALCULATE(AVERAGE('Kw/ton'[Chiller7_KW_Ton_value (kW)]),FILTER('Kw/ton','Kw/ton'[Chiller7_KW_Ton_value (kW)]>0.4 && 'Kw/ton'[Chiller7_KW_Ton_value (kW)]< 2),FILTER('Kw/ton','Kw/ton'[Date]= EARLIER('Kw/ton'[Date])))Paul Zheng
1 Reply
You cannot use if statement as the expression. To get the average between of all values between 0.4 and 2, try add the conditions in the filters:
CALCULATE(AVERAGE('Kw/ton'[Chiller7_KW_Ton_value (kW)]),FILTER('Kw/ton','Kw/ton'[Chiller7_KW_Ton_value (kW)]>0.4 && 'Kw/ton'[Chiller7_KW_Ton_value (kW)]< 2),FILTER('Kw/ton','Kw/ton'[Date]= EARLIER('Kw/ton'[Date])))Paul Zheng