DAX Formula for the excel function below

Anonymous — Fri, 27 Jan 2023 13:08:10 GMT

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))

Re: DAX Formula for the excel function below

v-jianboli-msft — Tue, 31 Jan 2023 02:40:00 GMT

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.

topic Re: DAX Formula for the excel function below in DAX Commands and Tips

DAX Formula for the excel function below

Re: DAX Formula for the excel function below