Highest 5 MAX Values in a single Column

[mg-66]

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

[v-eqin-msft · Accepted solution]

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]

Thanks for your Help @v-eqin-msft .

I appreciate your work.

[mg-66]

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))

[mg-66]

@amitchandak 

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

[mg-66]

The Data is separated in 3 Columns.

[amitchandak]

@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))