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

Summarizing row based data over 2 separate columns

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Summarizing row based data over 2 separate columns

Hi @daje,

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Summarizing row based data over 2 separate columns

Hi @daje,

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
daje Frequent Visitor
Frequent Visitor

Re: Summarizing row based data over 2 separate columns

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.