Forum Discussion
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.
Thanks!
Best Regards,
MG
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.
7 Replies
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.Thanks for your Help Anonymous .
I appreciate your work.
- amitchandak
Super User
mg-66 , Assume you need top 5 based on Date
Top5 = calculate(AverageX(values(Date[Date]), calculate(sum(Table1[Temperature]))),TOPN(5,allselected(Date[Date]),sum(Table1[Temperature]),DESC))
Thanks for your quick answer.
I need TOP 5 based just on thier Value.
- amitchandak
mg-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
