Summarizing row based data over 2 separate columns

[Anonymous]

I've got the following table in Power BI:

    Date  | PersonID | Hours  | Age
------------------------------|------
02-jan-18 |    4     |   8    |  3
06-jan-18 |    4     |   6    |  3
01-feb-18 |    4     |   6    |  3
05-feb-18 |    4     |   4    |  4
01-jan-18 |    5     |   6    |  3
01-feb-18 |    5     |   6    |  3

I have rows of data up until a few years back for multiple PersonID's. Most people have multiple rows per month because the data is split out on separate days. For every date, I have that person's age at the time (in this case, PersonID "4" had a birthday between feb 1st and feb 5th).

What I want to do is calculate the amount of hours PER MONTH, PER AGE. My end result should look something like this (average hours per month shown per age):

    Age | Average hours per month
----------------------------------
     1  |   35
     2  |   31
     3  |   28
     4  |   28

I have no idea how to get started. How can I calculate a sum over 2 columns?

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Based on my test, we can create a measure as below to meet your requirement.

Average hours per month = SUM(Table1[Hours])/COUNT(Table1[Month])

For more details, please check the pbix as attcehd.

https://www.dropbox.com/s/vrd05q3tf61cnwr/summ.pbix?dl=0

Regards,

Frank

[Anonymous]

Hi Frank,

Thank you so much for your help! I was getting wrong averages but your solution set me on the right path to fix it. I think it's because first I need to sum both per age and per month and I couldn't figure out how to do that.

So my final solution is slightly different. I made a calculated table that, for each month and age, did a distinct count of personid and a sum of hours per month. Rather than averaging the sum over month and age, I divided the total sum per age/month by the total distinct count per age/month. One example of how much that could change the result is an average went from ~35 to ~90 hours for one age group.