Measure average of top N values per user per hour

[Anonymous]

My data set looks like this:

Time    Date        ID        Value
12 am   1st Oct     1001      100
12 am   2nd Oct     1001      203
12 am   3rd Oct     1001      403
....    ......      ....      ....
11 pm   20th Oct    1001      566
12 am   1st Oct     1002      150
12 am   2nd Oct     1002      153
12 am   3rd Oct     1002      403
....    ......      ....      ....
11 pm   10th Oct    1002      666

For each customer ID, I have 20 days of data for each hour. I need to calculate and show the average of top 10 values for each hour out of that 20 days for each customer. For example, at 12 am, for 10001 customer ID, average will be the average of top 10 days between 1st oct and 20th oct. 

The output will look like this...

Time  ID     Average
12am  1001   105
1am   1001   056
...   ....   ...
11pm  1001   298
12am  1002   456
1am   1002   856
...    ...   ...
11pm  1002   166

How can I do that using Power BI? Being a beginner in Power BI, not sure where to start from.

[v-jialluo-msft]

Hi @Anonymous ,

Please follow these steps:
(1) Create a new measure

AVG = AVERAGEX(TOPN(10,'Table','Table'[Date],ASC),'Table'[Value])

(2)Final output

If it doesn't solve your problem, please provide example data

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Unfortunately, it didn't solve the problem. I solved it using rank.
1) rank the 20 days for each hour for each ID

Rank = RANKX(FILTER('table','table'[IDNumber]=EARLIER('table'[IDNumber])&&'table'[Time]=EARLIER('table'[Time])),'table'[lvalue],,,dense)

2) 

average = CALCULATE(AVERAGEX('table','table'[load]),('table'[Rank]<=15))

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523