Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
Solved! Go to Solution.
Hi @Soumik ,
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 @Soumik ,
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |