MDX Query to calculate Average rather than Sum

# MDX Query to calculate Average rather than Sum

• Saturday, November 24, 2012 11:23 AM

I have a cube that maintains ratings data per TV channel for every 15-min slot in the day (4 slots in hour / total 96 slots in day)

I have a RatingTime dimension which has two attributes

TimeSlot ===> represents 96 slots (06:00, 06:15, 06,30, 06:45, 07:00,..., 05:45)

Daypart ===> time periods in a 24-hour day (For example)

 Morning   (06:00-09:00) Daytime (09:00-16:00) Early Evening (16:00-20:00) Prime Time (20:00-23:00) Late Prime (23:00-01:00) Late Night (01:00-03:00) Early Morning (03:00-06:00)

The following MDX query will give me total rating per daypart:

SELECT [Measures].[Rating] ON COLUMNS,
[RatingTime].[Daypart].[Daypart] ON ROWS
FROM [MonthlyRatings]

Query Result:

 Daypart Rating Morning (06:00-09:00) 6.7 Daytime (09:00-16:00) 103.4 Early Evening (16:00-20:00) 146.0 Prime Time (20:00-23:00) 144.9 Late Prime (23:00-01:00) 92.3 Late Night (01:00-03:00) 35.3 Early Morning (03:00-06:00) 8.9

The above figures are actually the sum of all timeslots involved in each daypart (Please refer to images)

I need to get average rating per daypart rather than sum of rating, and this can perhaps be achieved by using the timeslot attribute of the RatingTime Dimension. If I can divide the sum of rating by the count of timeslots in each daypart. I'm not really an expert in MDX, but something like this:

SELECT ([Measures].[Rating] / COUNT [RatingTime].[TimeSlot].[TimeSlot])

ON COLUMNS,
[RatingTime].[Daypart].[Daypart] ON ROWS
FROM [MonthlyRatings]

I hope the above makes sense to you.

Any help is appreciated

Hani

### All Replies

• Saturday, November 24, 2012 7:21 PM

Hi,

```Avg(
Descendants(
[Manager].[Nom Complet].Currentmember
),
[Measures].[PA Salaire]
)```

See below link :

http://www.bigresource.com/MS_SQL-calculated-member-MDX-Average-problem-QNMDUg3O.html

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

• Saturday, November 24, 2012 8:13 PM

Many thanks Ahsan for your reply.

Below is my actual MDX query:

SELECT NON EMPTY [Measures].[Rating] ON COLUMNS,
NON EMPTY [Rating Time ORG].[Daypart].[Daypart] ON ROWS
FROM [MonthlyRatings]

Honestly speaking, I couldn't figure out how to apply your formula on my query due to my limited experience with MDX, so I would be so grateful if you could show me exactly how to apply it

Thanks again,

Hani

• Friday, November 30, 2012 4:53 PM

Create the average as a Caclulated Member within the scope of the query

```WITH MEMBER Measures.[Avg Rating] AS
Avg(
Descendants(
[Rating Time ORG].[Daypart].Currentmember
),
[Measures].[Rating]
)
SELECT NON EMPTY [Measures].[Avg Rating] ON COLUMNS,
NON EMPTY [Rating Time ORG].[Daypart].[Daypart] ON ROWS
FROM [MonthlyRatings]```