cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors