cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculating an average by person

I've been searching over and over, but I can't find the solution - so I'm afraid that there isn't any solution to this.

I have a table ("Time reports") with all reporting for our employes, grouped by Person and Date. The table looks like this example:

 

DatePersonBilled hours
2017-04-12Joe5
2017-04-13Joe8
2017-04-13Chris8,5
2017-04-13Dave6
2017-04-13Martin5
2017-04-14Chris7
2017-04-14Dave4
2017-04-14Martin5
2017-04-15Joe8
2017-04-15Chris8
2017-04-15Dave6
2017-04-16Martin9
2017-04-17Joe8
2017-04-17Chris8
2017-04-17Dave6
2017-04-17Martin5

 

I want to calculate the average of hours/day for each person individually. The average should be over the latest 10 reported days, per person.
Every person doesn't have reports for every day, so the latest 10 reported days won't be the same for every person.

 

In any other language I would have accompliced this by looping through the persons and count 10 days from a descending list - but how do I do in DAX or Power Query!?


One thought was to calculate this in the SQL-server - BUT, the table is merged from multiple data sources (Both SQL and Excel since we have multiple systems for time reporting) and each person can occur in both data sources.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Sage
Solution Sage

Assuming you only have the 'Person' on rows, then while i have not tested it this should work:

 

 

 

Avg per Person last 10 reported days = 
CALCULATE ( AVERAGE ( 'Time Reports'[Billed Hours] ); TOPN ( 10, VALUES ( 'Time Reports'[Date] ); LASTDATE ( 'Time Reports'[Date] ) ) )

 

View solution in original post

3 REPLIES 3
Solution Sage
Solution Sage

Assuming you only have the 'Person' on rows, then while i have not tested it this should work:

 

 

 

Avg per Person last 10 reported days = 
CALCULATE ( AVERAGE ( 'Time Reports'[Billed Hours] ); TOPN ( 10, VALUES ( 'Time Reports'[Date] ); LASTDATE ( 'Time Reports'[Date] ) ) )

 

View solution in original post

Hi @simonfalun,

 

I just verified that the formula provided by @mattbrice above should work in your scenario. Smiley Happy

 

@mattbrice, nice solution! Kudos +1. Smiley Wink

 

Regards

AWESOME! Thank you - you just turned my **bleep**ty day into a really great day!! Smiley Very Happy

I created a new table with only the persons and added a calculaded column with your code - worked like a charm!

 

Next step for me is to analyze the code so I can understand how it works Smiley Happy

 

Have a great day everyone!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors