Averaging Measure across Time

[BKhanna]

Is there a way to average a measure across every member of the time dimension instead of summing it? I have a measure, Headcount, and my time dimension has Quarters, Months and Days. When I select Month, for example, I would like to see an average of the headcount for all days in the month, and I would like to leave out days with a 0 headcount from being included in the average.

 

[justins57]

BK,

Yes, you can do this. Look at the NonEmptyCrossjoin function to only use days with a headcount value and the Avg function to give you the average for the headcount.

Something like this:

MEMBER [Measures].[Avg Headcount] As 'Avg(NonEmptyCrossjoin(Month.Children,{[Measures].[HeadCount]},1),[Measures].[HeadCount])'

hth,

Justin

 

[BKhanna]

Thanks for your help Justin. Although I didn't use the formula you mentioned, it did set me thinking in the right direction, and I got it to work the way I wanted. Thanks again! BK