How to create an average, rank, total measures over a field (by dynamic period) in SSAS?

Question

Hi everyone,

(I post here a question I created in stackoverflow, hopefully I will have better luck here)

I'm new with SSAS technology and I have an existing cube that measures hits by page on web site. In few words the cube is structured as followed:

• a dimension 'Expression' that contains reference to pages with DOI property as identifier.
• a dimension 'Date' to filter hits by period with hierarchical properties (year, month, day)
• a measure 'successful hits' that contains hits by DOI by date (one hit is recorded by timestamp, no aggregation by DOI)

So it's quiet simple to get results like this:

But I want also to get average, total and rank of hits by page and period like this (manually edited into Excel):

I tried to add a calculated measure to get the average by adding first a property to aggregate the count of hits : [Measures].[Hits count]

Then calculated [Average Hits] : [Measures].[Successful Hits]/[Measures].[Hits Count]

But all I get is 1 (because the [Measures].[Successful Hits] is equal to [Measures].[Hits Count]...)

How can I have :

• average of count by period
• total by period
• rank by item

Hope it's clear...

Thank you for your help!