Hello All,

I am facing some issues while performing calculations in MDX query. Let me give a brief description. I have a cube with-

Measure- Student Count
Date Time(dttm) Dimension with levels as year-Month-Day-Hour in descending order.

I am having Student Count data for three days on Hourly basis. I want to calculate the average student count/per hour for these three days. Like
for -
Date ---- Hour ---- Student Count
17/7 0 14
18/7 0 14
19/7 0 14

Then the average at hour 0 is (14+14+14)/3. I used the AVG function, but it didn't give me the average. Then used the concept of SUM/COUNT, but the SUM function took a DISTINCT VALUE 14 and instead of 14+14+14.

To Calculate Days-
with Member [Measures].[Day_Cnt] as
'Count([Date time.dttm].[Day].Members)'

To Calculate Average using SUM/Count-
WITH member [Measures].[Avg_Students] as
'Sum({[Date time.dttm].[Hour].CurrentMember}, [Measures].[Student Count])/[Measures].[Day_Cnt]'

Using AVG-

WITH MEMBER [Measures].[Avg_Students] as
'AVG({[Date time.dttm].[Hour].CurrentMember}, [Measures].[Student Count])'


Could you please guide me with this issue.

Many Thanks in Advance.