Finding the daily average of hourly averages (Weighted average)

[Anonymous]

Hi!

So I am looking at data that is grouped by hourly increments in military time. There is a counting program on the metro, and we collect the time interval that people are counted at, and round it to the hour. So I took the average of how many people are counted each hour, calculated from all the observations. For example if we have 3 observations at 2 am, 65 people counted, 27 people counted and 12 people counted (all these observations are being taken by different people, than the average hourly count for 2 am would be 34.7 people. 

Now I am trying to get a daily average. But instead of just adding up all the observations for each hour and dividing by the number to get an unweighted average, I want to get a weighted average where each hour is given equal weight. Because some hours have more observations than others and I don't want to give them unequal weight just because there were more observations.

So I am curious, how would I get a weighted average, the average of an average basically, taking into account each hour equally? So say I get an average of 34.7 people for 2 am, 51 people for 3 am, 12 people for 4 am etc., how do I average all of those together to get the total daily average? I have just been doing this in excel, getting the average for each hour and then dividing that by the total number of hours that had any observations taken, (oh yes also I don't think that it should be divided by 24, because not all hours have observations, so I believe it would be divided by 16 etc. if only 16 hours had observations). But I would like to know how to do this in power bi. Starting out with just individual counts that counters have taken at different time intervals. For example Counter #1 counted 5 people at 6 am, Counter #2 counted 21 people at 7 am, Counter #3 counted 55 people at 6 am, Counter #4 counted 25 people at 8 am, Counter #5 counted 34 people at 6 am etc.

Sorry, I can't post the original data because my workplace forbids it. But please let me know if anything is unclear, or if further explanation is needed. Thanks so much in advance for the help 🙂

[johnt75 · Accepted solution]

You could use a summary table in a measure, like

Daily Avg =
var summaryTable = ADDCOLUMNS( VALUES('observations'[hour]), "@value", [Hourly average])
return AVERAGEX( summaryTable, [@value])

[Anonymous]

Thanks so much!!