Average of best 10% applied filters

[baribir]

Hi All,

I want to calculate the average of top 10% rounded up, of speeds by the operator. If crew filter selected the top 10% filtered by crew and if type selected top 10% filtered by crew and type.

Thanks in advance.

Sample file

[Fowmy · Accepted solution]

@baribir 

You use this measure to get the Average of Top 10 Full Speed, Other filters will also work.

Top 10 Speed = 

AVERAGEX(
    TOPN(10,Sheet1,Sheet1[FullSpeed],DESC),Sheet1[FullSpeed]
)

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[harshnathani]

Hi @baribir ,

See if this helps

https://www.youtube.com/watch?v=SsZseKOgrWQ

https://www.youtube.com/watch?v=UAnylK9bm1I

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Fowmy]

@baribir 

Do you need the Top 10 speeds rounded up and averaged by Operator?

Thanks

[baribir]

Average of Rounded top 10% of the count of best speeds.

[Fowmy]

@baribir 

Please check this measure:

Top 10 Speed = 
VAR T10 = COUNTROWS(Sheet1) * 0.1

RETURN
AVERAGEX(
FILTER(
    Sheet1,
     RANKX(Sheet1,CALCULATE(MAX(Sheet1[FullSpeed])),,DESC)<=T10
),
Sheet1[FullSpeed]
)

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel