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(COUN...
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.
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.