Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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 quick answer.
I need TOP 5 based just on thier Value.
@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
The Data is separated in 3 Columns.
@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))
User | Count |
---|---|
86 | |
82 | |
68 | |
66 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |