Forum Discussion
Calculating yearly average from monthly average
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
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
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!
MasterPGPlease 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