Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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.