Calculating yearly average from 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!

[Anonymous · Accepted solution]

You are trying to create a measure that will be calculated differently on different levels of a hierarchy. Therefore this measure will be tightly connected to this hierarchy and should not be used with any other level - just: Date, Month, and Year. If you use it on a different level, the calculation will be wrong. In DAX, hierarchies are not a strong point. Here's the measure but you should not use it with any other level than just the 3 mentioned. If you want to calculate on any other level (for instance, for all years as the average of the measure for the years), you have to make changes to it.

// Hidden measure
[_AverageInOneDay] =
	AVERAGE( Data[Measurement] )

// Hidden measure
[_AverageInOneMonth] =
	AVERAGEX(
		VALUES( Calendar[Date] ),
		[_AverageInOneDay]
	)
	
// Hidden measure
[_AverageInOneYear] =
	AVERAGEX(
		VALUES( Calendar[Year Month] ),
		[_AverageInOneMonth]
	)
	
// Final measure exposed
[Measurement] =
SWITCH( true(),
	ISINSCOPE( Calendar[Date] ),
		[_AverageInOneDay],
	ISINSCOPE( Calendar[Year Month] ),
		[_AverageInOneMonth],
	ISINSCOPE( Calendar[Year] ),
		[_AverageInOneYear]
)

Best

D

[MasterPG5]

Thank you very much! Exactly what I needed.

Just one question: I do not completely understand the mechanisms with date tables. You said that I should not use the last measure on any level except day, month, year. This may be nothing, but it is also working with YearMonth instead of Month, giving the same result. Is this the expected behaviour?

Forget that, it is not giving the same result, now I see.

Thanks again for helping me out!
MasterPG

[Anonymous]

Please bear in mind that when I say 'Year Month' it means that you have to have exactly one month exposed from a year. You cannot just drop a month's name because January is present in all years. You have to have something like 2020-January as this identifies the month uniquely across the whole of the Calendar. Of course, if you put a hierarchy in a matrix: Year -> Month -> Date, it'll calculate OK because on each level either one date will be visible or exactly one month or exactly one year. It's all about it: you have to make sure that in the current context only one of those objects is visible. Then and only then will the calculation be correct.

Of course, you could use a lot of conditional code to make sure that the measure returns nothing when anything else than the three objects has been detected but I'm not sure you need this. As long as you know how to use the measure, you're safe. But, on the other hand, if you want to expose this measure to the end-users so that they are free to use it, then it's of utmost importance to make the measure behave correctly in all circumstances.

Best
D