cancel
Showing results for
Did you mean:
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

## 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

## 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.
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.