Forum Discussion
Highest 5 MAX Values in a single Column
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.
Super Usermg-66 , if you do not have any other column than temperature, then add index column in power query and create this measure
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
Top5 = calculate(AverageX(values(Table[Index]), calculate(sum(Table1[Temperature]))),TOPN(5,allselected(Table[Index]),sum(Table1[Temperature]),DESC))
I am doing something wrong, it's not calculating the average. So I will show you how my Data looks like and in what I am interested exactly.
| Date | Time | Tmax |
| 05.11.2021 | 10:26:04.159 | 86,7 |
| 05.11.2021 | 10:26:05.457 | 104,7 |
| 05.11.2021 | 10:26:06.692 | 118,3 |
| 05.11.2021 | 10:26:07.842 | 97,4 |
| 05.11.2021 | 10:26:09.029 | 82,6 |
| 05.11.2021 | 10:26:10.209 | 88,3 |
| 05.11.2021 | 10:26:11.387 | 106 |
| 05.11.2021 | 10:26:12.578 | 114,4 |
| 05.11.2021 | 10:26:13.741 | 108,4 |
| 05.11.2021 | 12:44:42.113 | 84,2 |
| 05.11.2021 | 12:44:43.957 | 95,4 |
| 05.11.2021 | 12:44:45.889 | 111,4 |
| 05.11.2021 | 12:44:47.555 | 104,6 |
| 05.11.2021 | 12:44:49.326 | 115,8 |
| 05.11.2021 | 12:44:50.962 | 131,6 |
| 05.11.2021 | 12:44:52.636 | 111,9 |
| 05.11.2021 | 12:44:54.184 | 111,9 |
| 05.11.2021 | 12:44:55.841 | 104,9 |
| 05.11.2021 | 12:44:57.458 | 112,7 |
| 05.11.2021 | 12:44:59.142 | 107,4 |
| 05.11.2021 | 12:45:00.809 | 123,9 |
| 05.11.2021 | 12:45:02.464 | 111 |
| 05.11.2021 | 12:45:04.132 | 111,8 |
| 05.11.2021 | 12:45:05.787 | 111,7 |
| 05.11.2021 | 12:45:07.362 | 121,5 |
The Blue Measurements were taken at a different time. I have a filter in my Layout to distinguish between these Measurements.
So I want to pick the highest 5 Values of Tmax and calculate the average from these values. My integrated Filter will automatically use just one of the Measurements (the marked black or marked blue ones).
The Average of my top 5 peaks shoud be: 110,36 °C
The Data is separated in 3 Columns.