Forum Discussion
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 | 28I have no idea how to get started. How can I calculate a sum over 2 columns?
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
2 Replies
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
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.