Forum Discussion
Calculate Average from highest X values
Hello everyone,
I am new to Power BI and could need your help.
I have a Dataset that consistes of many tables and looks now like this:
| Date | Temp. | ..... |
| ..... | 105,2 | ..... |
| ..... | 103,3 | ..... |
| ..... | 98,7 | ..... |
| ..... | 99,1 | ..... |
| ..... | 101,5 | ..... |
| ..... | 99,8 | ..... |
The other columns should be irrelevant for now. The Time at which these measures where taken doesn't play a role for me.
I wand to add a measure to calculate the average of my 2 highest Temp. values.
Maybe we can order this Column in a descending order and choose the top values to build then the average from them.
Thanks for your help and explanation.
Best Regards
mg-66 , Create a column Rank
R1= Rankx(Table, [Temp],,desc, dense)
Then create a measure
Averagex(filter(Table, Table[R1]<=2), Table[Temp])
3 Replies
- amitchandak
Super User
mg-66 , Create a column Rank
R1= Rankx(Table, [Temp],,desc, dense)
Then create a measure
Averagex(filter(Table, Table[R1]<=2), Table[Temp])
amitchandak thank you for your Answer again. But this approach is not working. I also can not tell why or whats mistake I'm doing maybe.
amitchandak thanks alot, it works now 😀.
I would like to add something.
I want to do the same, but sort the Temp. according to their day and to their Starting Time (ST). Like in this table for example:
Day ST Temp Rank 1 8am 105,2 1 1 8am 103,3 2 1 8am 98,7
3 1 10am 99,1 3 1 10am 101,5 1 1 10am 99,8 2 2 8am 105,4 1 2 8am 100,3 2 2 8am 96,7 3 2 10am 97,7 2 2 10am 98,8 1 Then I will calculate the average in the same way by clicking the Day & Starting Time (ST) Slicer/Filter in the report.
