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

Lanz 0 Reputation points
2023-10-05T08:27:57.2966667+00:00

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:

hits

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

hitsWithAvg

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!

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes