I have a dimension:
- Not Completed
and a dimension:
- No 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.
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?