Forum Discussion

mg-66's avatar
mg-66
Regular Visitor
4 years ago
Solved

Highest 5 MAX Values in a single Column

Hello Guys,   I need your Help. I have a Column with Temperature Values we measured. I would like to calculate the average of my highest 5 values for example.   I would appreciate every answer. ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi mg-66 ,

     

    According to my understanding, you want to calculate the top5 average of each Time(same Hour and Minute), right?

    Please use the following formula to create Rank and Average measure:

    Rank = RANKX(FILTER(ALL('Table'), HOUR([Time])=HOUR(MAX('Table'[Time])) && MINUTE([Time])=MINUTE(MAX('Table'[Time]))),  CALCULATE ( MAX ( ( 'Table'[Tmax]) ) ),,DESC,Dense)
    Average = CALCULATE(AVERAGE('Table'[Tmax]),FILTER(ALL('Table'),  HOUR([Time])=HOUR(MAX('Table'[Time])) && MINUTE([Time])=MINUTE(MAX('Table'[Time]))&& [Rank]<=5))

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi mg-66 ,

 

According to my understanding, you want to calculate the top5 average of each Time(same Hour and Minute), right?

Please use the following formula to create Rank and Average measure:

Rank = RANKX(FILTER(ALL('Table'), HOUR([Time])=HOUR(MAX('Table'[Time])) && MINUTE([Time])=MINUTE(MAX('Table'[Time]))),  CALCULATE ( MAX ( ( 'Table'[Tmax]) ) ),,DESC,Dense)
Average = CALCULATE(AVERAGE('Table'[Tmax]),FILTER(ALL('Table'),  HOUR([Time])=HOUR(MAX('Table'[Time])) && MINUTE([Time])=MINUTE(MAX('Table'[Time]))&& [Rank]<=5))

Output:

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mg-66's avatar
mg-66
Regular Visitor
4 years ago

Thanks for your Help Anonymous .

I appreciate your work.