Guys,

Ok, I am using SSAS 2005 and have created a calculated measure that
returns a simple average of the number of licenses in a given space:

[Measures].[MSC Licenses]/[Measures].[MSC Organization Count]

Now I need to turn this into a weighted average - something that will
exclude the top and bottom 25% of results in the space BEFORE coming up
with the average. I have the following SQL example if it helps (generic
example - not related to my fact/dimensions):

Declare @TestScores table (StudentID int, Score int)

insert @TestScores (StudentID, Score) Values (1, 20)
insert @TestScores (StudentID, Score) Values (2, 03)
insert @TestScores (StudentID, Score) Values (3, 40)
insert @TestScores (StudentID, Score) Values (4, 45)
insert @TestScores (StudentID, Score) Values (5, 50)
insert @TestScores (StudentID, Score) Values (6, 20)
insert @TestScores (StudentID, Score) Values (7, 90)
insert @TestScores (StudentID, Score) Values (8, 20)
insert @TestScores (StudentID, Score) Values (9, 11)
insert @TestScores (StudentID, Score) Values (10, 30)

-- the avgerage score is thrown off by the curve-wrecking student who
got a 90!
select [Straight Curve Average] = avg(cast(Score as float))
from @TestScores

-- Trimming the smallest and largest percentile
-- A more general approach is to trim by a fixed percentage instead of
a fixed number. Here we trim by a factor between 0.0 and 0.5. Trimming
by 0.0 trims nothing yielding the mean. Trimming by .25 discards the
scores in the top and bottom quarters and averages what's left.
Trimming by .5 yields the weighted median. The median is weighted when
there are duplicate values. In this example the central values are
20,20,20,30 which average out to 22.5. Compare this to the non-weighted
median 25.0 ((20+30) / 2).

declare @pp float
set @pp = .25

select @pp as factor,
(sum(cast(score as float) * weight)/ sum(weight)) as TrimmedMeanP2
from (
select a.score,
count(*) as weight
from @TestScores a
cross join @TestScores b
group by a.score
having sum(case when b.Score <= a.Score then 1 else 0 end) >= @pp *
count(*)
and sum(case when b.Score >= a.Score then 1 else 0 end) >= @pp *
count(*)
) x1

Is something like this possible in MDX with one calculated member? What
would it look like?

Thanx for all your help!

Sincerely,

J'son

A past thread from this Newsgroup discusses a similar "trimmed" average
problem, so those solutions may work for you - albeit with poor
performance:

http://groups.google.com/group/micro...olap/msg/1fab5
c2ab767b87d
...
To exclude exceptions that have a huge impact on the average I
want to create a trimmed average. That is, I want to exclude the top 25%
values in my trimmed average measure, which are the 2 records with the
highest ThroughputTime, being ordernumber 00007 and 00008 in the "All
Certificate" & "All Completed" row, but different in the other rows. I
also
want the bottom 25% values to be trimmed, which are the 2 records with
the
lowest ThroughputTime, being ordernumber 00001 and 0003 in the "All
Certificate" & "All Completed" row.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanx Deepak... I'll check it out.

J'son


Deepak Puri wrote:

Deepak (or someone),

Ok, I used the script provided in the link and got it working. My
modifed script looks like this:

Avg(Except(Except(Filter([Organization].[Organization].[Organization].Members,
Not IsEmpty(
[Measures].[MSC Licenses])),
TopCount([Organization].[Organization].[Organization].Members,
Count(Filter([Organization].[Organization].[Organization].Members, Not
IsEmpty([Measures].[MSC Licenses])))/4, [Measures].[MSC Licenses])),
BottomCount(Filter([Organization].[Organization].[Organization].Members,
Not IsEmpty([Measures].[MSC Licenses])),
Count(Filter([Organization].[Organization].[Organization].Members, Not
IsEmpty([Measures].[MSC Licenses])))/4,
[Measures].[MSC Licenses])), [Measures].[MSC Licenses])

Pretty convoluted but it works and its not too slow. However, when I
want to filter this in the Cube browser by Segment (an attribute of the
Organization dimension), the results don't change! I know that
[Organization].[Organization].[Organization].Members will get me ALL
members, but how do have the calculation reflect just the Organizations
I'm filtering by?

Thanx again!

J'son

sitexcite (AT) hotmail (DOT) com wrote:

You can try to filter with "Existing", like:

Existing [Organization].[Organization].[Organization].Members,
Not IsEmpty(
[Measures].[MSC Licenses])),
TopCount(
Existing [Organization].[Organization].[Organization].Members,
Count(Filter(
Existing [Organization].[Organization].[Organization].Members,
Not IsEmpty([Measures].[MSC Licenses])))/4,
[Measures].[MSC Licenses])),
BottomCount(Filter(
Existing [Organization].[Organization].[Organization].Members,
Not IsEmpty([Measures].[MSC Licenses])),
Count(Filter(
Existing [Organization].[Organization].[Organization].Members,
Not IsEmpty([Measures].[MSC Licenses])))/4,
[Measures].[MSC Licenses])), [Measures].[MSC Licenses])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Nope... still no change in the results. Thanx though.. I really had
my hopes up! :P I hate learning a product from the top down.

Is there a reason why Existing wouldn't work?

J'son

Deepak Puri wrote: