Calculation of the annual average from the monthly average

[MasterPG5]

Hi everyone,

I'm quite new to PowerBI, so my problem may be quite easy to solve, but I didn't really find a suitable answer yet.

So I have lots of geographical data from a 40 year long period with exact measurement times. However, measurements were not always made with the same frequency. For example, when the values were high, measurements were carried out more often.

What I would like to do is show the averages on a line diagram (versus date). My problem is, if I apply the normal "average" summarization on the data, it calculates the average from all data on the given period (based on the hierarchy level).

An example:

Normally 1 measurement is made on a day. That's 30-31 in a month, 356 in a year. However, in summer, the values are higher, so sometimes more measurements are carried out, occasionally even 4 an hour. That can be as many as 96 record a day. If yearly average is calculated, a few summer day can pull the value much higher than they should - that gives some unusable data.

What I need is, to calculate the daily average for every day, than calculate the monthly average from the daily averages of that month, yearly average from the monthly averages of that year, and then a "total" average from the yearly averages. I would like to use it on a line diagram while keeping date hierarchies (and drill-down) usable.

How can I do that? I've read some posts mentioning the averagex function, but couldn't figure out the solution, can it be used in my case?

Thanks for your help in advance!

[MFelix · Accepted solution]

Hi @MasterPG5 ,

What you need to use is an aggregator formula.

Aggregator formulas (the ones that end in X) make the calculation based on each line of a table what this means is that if your are making the average of values for each line it will pick up the values and use it to calculate the average.

Picking up on your example:

Day	Value
1	10
1	11
1	9
2	12
3	13

If you do just the average you will get:

(10+11+9+12+13)/ 5 = 11

if you use the aggregator on day level what you will get:

((10+11+9)/3) + (12 / 1) + (13/1) =  11,66

So basically you need to do the average of the averages based on day. Having done the theorical explanation the measure you need to create is the following:

Average = AVERAGEX(Table; AVERAGE(Table[Column])

Not sure if you have a calendar table but if you do you need to use it for the first parameter then use the column for wich you want to have the calculation made.

Being a measure this will be calculated based on context so when you move up and then the hierarchy ( year, month, day) the calculations will adjust.

[MasterPG5]

Thank you very much for your help! I have actually got an answer already on the English forum (I don't even know why my question popped up here...), but your solution is also perfect.

Thanks, man!