Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    12

    Unanswered: MSAS: Trimmed Average

    Hi,

    I have the following table:
    Code:
    OrderNumber varchar(15)
    Completed int(1) 
    Certificate int(1)
    ThroughputTime int(4)
    With the following data:
    OrderNumber Completed Certificate ThroughputTime
    Code:
    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:

    Code:
                                                    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:
    Code:
                                                    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!

  2. #2
    Join Date
    May 2006
    Posts
    12
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •