DAX Formula for the excel function below

[Anonymous]

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

[v-jianboli-msft · Accepted solution]

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.