Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Measure average of top N values per user per hour

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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))
3 REPLIES 3
Community Support

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
Not applicable

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

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors