All,

Consider this data:

Run RunCount Team TeamCount Year
(measure) (measure)
-------------------------------------------------
HomeRun 1 Team1 1 2001
Single 1 Team1 0 2001
Walk 1 Team1 0 2001
HomeRun 1 Team1 0 2001
SacFly 1 Team1 0 2001
Double 1 Team1 0 2001
Walk 1 Team1 1 2002
Walk 1 Team1 0 2002
SacFly 1 Team1 0 2002
Single 1 Team1 0 2002
HomeRun 1 Team1 1 2003
Double 1 Team1 0 2003
Walk 1 Team1 0 2003
Single 1 Team1 0 2003
Single 1 Team1 0 2003

Using this Fact Table, I am able to get the average runs (RunCount)
for Team 1 for Year 2002 and 2003. When I try to slice the data by Run
type "Single", I do not get the right average for Year 2002 and 2003.
This is because the TeamCount may be 1 or 0 for Run type "Single"
which depends on the ordering of Run within a Year. So if "Single" is
a 0 for 2002 I get an error. If its not then I get the right count.
The main goal here is to be able to combine averages over more than
one Year. We use proclarity which provides this functionality.

The problem I am having is how to handle the "Single" slice? Any ideas
or pointers on where I can read up on or take a look at samples? I am
stuck and just need a push in the right direction.

Thanks,
brian

Is the Team Count measure intended to try and count the years in a given
slice of the cube? If so, you should use one of the well-known
semi-additive measure techniques:

http://groups.google.com/groups?q=se...roup:microsoft.
public.sqlserver.olap&start=20&hl=en&lr=&ie=UTF-8&group=microsoft.public
.sqlserver.olap&selm=OEHKqCN9CHA.3412%40TK2MSFTNGP 11.phx.gbl&rnum=25
Subject: Re: Average over Time Dim with MDX
View: Complete Thread (4 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-03-27 18:22:19 PST

Sounds like the classic semi-additive measure requirement. You can refer
to this Microsoft Tech-Ed 2002 PowerPoint:

http://technet.microsoft.at/includes/file.asp?ID=3336
Slide 49>>
1. Average over time

Sum of quantities over all months in the period, divided by the number
of months in the period

Sum( months in the period , Quantity) / Count( months in the period )

Sum(Descendants( Time.CurrentMember,[Month]), Quantity) /
Count(Descendants( Time.CurrentMember,[Month]))

Avg( Descendants( Time.CurrentMember,[Month]), Quantity)

- Deepak

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak,

Thanks for you reply.

Team Count is meant to get a unique count of teams within a year. Only
one record for each team in a year is marked with 1. I am having a
problem when the user drills down the Run dimension.

Any other ideas would be appreciated. Thanks.

-Brian

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

What is the average MDX formula that's causing the problem - I assume
that it's (RunCount)/(TeamCount)? In that case, try defining
Measures.TeamCount as a calculation as below,
rather than aggregating it as a cube measure.

Descendants(Team.CurrentMember,[Team])).Count

- Deepak

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!