cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## The average of top 2 for grouped data

Hi everyone,

I would like to calculate the average value of top two values for every section per year&month,

I have tried ranking them first, but I couldn't have a correct ranking.

Do you guys have an idea of how to do it?

2 ACCEPTED SOLUTIONS
Community Champion

Hi @nqahman

Try this MEASURE

```Measure =
AVERAGEX (
TOPN (
2,
FILTER (
ALL ( TableName ),
TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] )
&& TableName[MONTH] = SELECTEDVALUE ( TableName[MONTH] )
&& TableName[Section] = SELECTEDVALUE ( TableName[Section] )
),
TableName[VALUE], DESC
),
CALCULATE ( SUM ( TableName[VALUE] ) )
)```

Regards
Zubair

Community Champion

@nqahman

Please see the attached file here

Regards
Zubair

6 REPLIES 6
Community Champion

Hi @nqahman

Try this MEASURE

```Measure =
AVERAGEX (
TOPN (
2,
FILTER (
ALL ( TableName ),
TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] )
&& TableName[MONTH] = SELECTEDVALUE ( TableName[MONTH] )
&& TableName[Section] = SELECTEDVALUE ( TableName[Section] )
),
TableName[VALUE], DESC
),
CALCULATE ( SUM ( TableName[VALUE] ) )
)```

Regards
Zubair

Regular Visitor

An error exists because of too many arguments in the FILTER function.

The maximum number is 2 arguments: Table name and one expression.

Community Champion

@nqahman

I have only 2 arguments in above formula for FILTER

1)

`ALL ( TableName )`

2)

``` TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] )
&& TableName[MONTH] = SELECTEDVALUE ( TableName[MONTH] )
&& TableName[Section] = SELECTEDVALUE ( TableName[Section] )```

Regards
Zubair

Community Champion

@nqahman

Please see the attached file here

Regards
Zubair

Regular Visitor

Thank you so much @Zubair_Muhammad. Finally, it's working.
Appreciated.

Community Champion

@nqahman

Regards
Zubair