Forum Discussion

simonfalun's avatar
simonfalun
Frequent Visitor
9 years ago
Solved

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...
  • mattbrice's avatar
    mattbrice
    9 years ago

    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] ) )  
)

     

mattbrice's avatar
mattbrice
Icon for Solution Sage rankSolution Sage
9 years ago

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] ) )  
)

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi simonfalun,

     

    I just verified that the formula provided by mattbrice above should work in your scenario. :smileyhappy:

     

    mattbrice, nice solution! Kudos +1. :smileywink:

     

    Regards

  • simonfalun's avatar
    simonfalun
    Frequent Visitor
    9 years ago

    AWESOME! Thank you - you just turned my **bleep**ty day into a really great day!! :smileyvery-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 :smileyhappy:

     

    Have a great day everyone!