Forum Discussion
DAX Formula for the excel function below
Could you please help me with the equivalent DAX formula/code for the below mentioned Excel function
=AVERAGE(LARGE(Sheet1!A2:A21, INT((COUNT(Sheet1!A2:A21)-1)/4)+1),LARGE(Sheet1!A2:A21, INT(COUNT(Sheet1!A2:A21)/4)+1))
Hi Anonymous ,
Based on your description, I have created a simple sample:
Please try:
FIrst creeate an index column:
Then apply the measure:
Measure = VAR _a = FILTER ( 'Table', [Index] >= 1 && [Index] <= 20 ) VAR _b = INT ( ( CALCULATE ( COUNT ( 'Table'[Number] ), _a ) - 1 ) / 4 ) + 1 VAR _c = CALCULATE ( MAX ( 'Table'[Number] ), FILTER ( _a, RANKX ( 'Table', [Number] ) = _b ) ) VAR _d = INT ( CALCULATE ( COUNT ( 'Table'[Number] ), _a ) / 4 ) + 1 VAR _e = CALCULATE ( MAX ( 'Table'[Number] ), FILTER ( _a, RANKX ( 'Table', [Number] ) = _d ) ) RETURN AVERAGEX ( { _c, _e }, [Value] )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
Based on your description, I have created a simple sample:
Please try:
FIrst creeate an index column:
Then apply the measure:
Measure = VAR _a = FILTER ( 'Table', [Index] >= 1 && [Index] <= 20 ) VAR _b = INT ( ( CALCULATE ( COUNT ( 'Table'[Number] ), _a ) - 1 ) / 4 ) + 1 VAR _c = CALCULATE ( MAX ( 'Table'[Number] ), FILTER ( _a, RANKX ( 'Table', [Number] ) = _b ) ) VAR _d = INT ( CALCULATE ( COUNT ( 'Table'[Number] ), _a ) / 4 ) + 1 VAR _e = CALCULATE ( MAX ( 'Table'[Number] ), FILTER ( _a, RANKX ( 'Table', [Number] ) = _d ) ) RETURN AVERAGEX ( { _c, _e }, [Value] )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.