# Database Discussions > MDX and Analysis Services >  MSAS Trimmed Average

## Edwardvb

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 a measures:
TptSum which Sums ThroughputTime
TptCount which Counts OrderNumbers
TptAvg which divided 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 a 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  :Wink: 

Thanks,

Edward

----------


## Edwardvb

I found a solution for my problem.... however .... it's incredibly slow on a large DB (500,000 records)... any ideas?

I created an extra dimenion called Orders with all ordernumbers listed...

after that I created a Calculated Member called "Trimmed Average" with the following syntax:

Avg(Except(Except(Filter(Orders.Ordernumber.Member  s, Not IsEmpty(Measures.TptSum)), TopCount(Orders.Ordernumber.Members, Count(Filter(Orders.Ordernumber.Members, Not IsEmpty(Measures.TptSum)))/4, Measures.TptSum)),BottomCount(Filter(Orders.Ordern  umber.Members, Not IsEmpty(Measures.TptSum)), Count(Filter(Orders.Ordernumber.Members, Not IsEmpty(Measures.TptSum)))/4, Measures.TptSum)), Measures.TptSum)

The calculated member does exactly what I want... however, for my DB it takes about 4 minutes to browse the data in MSAS... and when I change my selection, I again have to wait for about 4 minutes.... Any ideas of how to speed this thing up?

----------

