MSAS: Trimmed Average

Hi,

I have the following table:
OrderNumber varchar(15)
Completed int(1)
Certificate int(1)
ThroughputTime int(4)

With the following data:
OrderNumber Completed Certificate ThroughputTime
00001 1 1 1
00002 1 1 2
00003 1 0 1
00004 1 0 1
00005 0 1 2
00006 0 1 1
00007 0 0 4
00008 0 0 89

I have a dimension:
All Completed
- Completed
- Not Completed

and a dimension:
All Certificate
- No Certificate
- Certificate

I also have measures:
TptSum which Sums ThroughputTime
TptCount which Counts OrderNumbers
TptAvg which divides TptSum by TptCount

My problem is with the next measure:
In MSAS I created a Cube which shows the measures on the columns and both Completed and Certificate on the rows. The OrderNumber selection is set to "All Ordernumbers". The measures as listed above are displayed exactly as I expect them to do. TptSum gives the sum for the subselection showed on the left. TptCount and TptAvg also show the expected values on their distinct rows.
I can't upload a screenshot but it looks like this:


TptSum TptCount TptAvg
All Certificate All Completed 101 8 12.63
Completed 5 4 1.25
Not Completed 96 4 24.00
No Certificate All Completed 95 4 23.75
Completed 2 2 1.00
Not Completed 93 2 46.50
Certificate All Completed 6 4 1.50
Completed 3 2 1.50
Not Completed 3 2 1.50


So far so good. Each datarow only takes the records that match the criteria at the left.
Now however, 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.

I tried a lot of things, but i can't get it working. Somehow all my statements don't take the subselections on the rows into account. Really frustrating.

What I want to achieve is the following:

TptSum TptCount TptAvg TrimmedAvg
All Certificate All Completed 101 8 12.63 1.50 ((2+1+2+1) / 4) (middle 4 records)
Completed 5 4 1.25 1.00 ((1+1) / 2) (middle 2 records)
Not Completed 96 4 24.00 3.00 ((2+4) / 2) (middle 2 records)
No Certificate All Completed 95 4 23.75 2.50 ((1+4) / 2) (middle 2 records)
Completed 2 2 1.00 1.00 ((1+1) / 2) (all 2 records)
Not Completed 93 2 46.50 46.50 ((4+89) / 2) (all 2 records)
Certificate All Completed 6 4 1.50 1.50 ((1+2) / 2) (middle 2 records)
Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
Not Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)

I hope I describe my problem well... I also hope someone knows a solution. I can also post the things I tried, but I'm afraid that makes it more confusing, because nothing worked ;)

Thanks,

Edward

PS. newbie alert! :beer: