none
Median() function doesn't work as expected.

    Question

  • HI

    I hope anyone can actually help me to slove my problem.

    I am creating a calculated member by using Median() function.

    The calculated member is Median(Sample.SampleID.members, Value).

    Sample and Material are two different dimensions, which are linked to Fact.Value.

    As you can see the correct median value over 4 sampleID and 1 material should be 1.567.  However, the MDX median gives me 1.713. It seems like that MDX median function() ignores the first SampleID and only apply median to the rest 3 Samples.

    Does anybody know the reason?

    Thursday, March 21, 2013 5:04 PM

Answers

All replies

  • Can you post the MDX?
    Thursday, March 21, 2013 5:22 PM
  • Can you post the MDX?

    It is not something speical.

    Just like this

    CREATE MEMBER CURRENTCUBE.[Measures].[Value - Median]
     AS 
        Median(
            [Sample].[SampleID].members,
            [Measures].[Value]
            ), 
    FORMAT_STRING = "#,#.000", 
    VISIBLE = 1;   

    I have done lots of testing No matter what combinations of dimension I choose, median function only calculates from the second Sample to the last one.

    It always ignore the first sample 

    Thursday, March 21, 2013 5:46 PM
  • Can you post the MDX?

    I want to choose the median value figure over all Samples and also can slice the figures based on different dimensions. 

    In this case, MDX should be applied to Sample members, right?

        Median(
            [Sample].[SampleID].members,
            [Measures].Value
            ), 

    Thursday, March 21, 2013 5:48 PM
  • I have tried to replicate this behavior but have not been successful.

    For a set with an odd number of results, Median always return the middle value.  For a set with an even number of results, Median adds the 2 middle numbers together and divides the result by 2.  In my data, this behavior is confirmed.

    Thursday, March 21, 2013 5:51 PM
  • I have tried to replicate this behavior but have not been successful.

    For a set with an odd number of results, Median always return the middle value.  For a set with an even number of results, Median adds the 2 middle numbers together and divides the result by 2.  In my data, this behavior is confirmed.

    Hi Bendare

    I understand how Median function works.  It just does not give me the correct value.

    I have another screenshot.

    If I remove sample dimension from the selection, the correct median value for Material101 should be 6.558 and Material102 should be 6.052 according the correct logic of Median.   

    However,  When I use the following calculated member over Material 101 and 102. 

    The calculated Median value for Material 101 is 10.108.   Obviously, it skip the first value 1.989 and only applies Median to (3.008,  10.108,  62.875). Then Median Value is the middle one 10.108,which is wrong.

    The calculated Median value for Material 102 is 17.6. It skip the first value 1.94 and only calculates based on (6.052,   27.147). The Median is the average of the two figures 17.6, which is also wrong.

    CREATE MEMBER CURRENTCUBE.[Measures].[Value - Median]
     AS 
        Median(
            [Sample].[SampleID].members,
            [Measures].[Value]
            ), 
    FORMAT_STRING = "#,#.000", 
    VISIBLE = 1;  

    I just dont know why the calculation behaves like this.

    Thursday, March 21, 2013 6:13 PM
  • Can you post the MDX?

    I want to choose the median value figure over all Samples and also can slice the figures based on different dimensions. 

    In this case, MDX should be applied to Sample members, right?

        Median(
            [Sample].[SampleID].members,
            [Measures].Value
            ), 

    It's got a wrong count somehow.  I suspect that it's not ignoring the first value, it's adding an additional count.  Can you try changing it like below to see if it alters the results?

        Median(
            Descendants([Sample].[SampleID].currentmember),
            [Measures].Value
            ), 

    Thursday, March 21, 2013 6:48 PM
  • Can you post the MDX?

    I want to choose the median value figure over all Samples and also can slice the figures based on different dimensions. 

    In this case, MDX should be applied to Sample members, right?

        Median(
            [Sample].[SampleID].members,
            [Measures].Value
            ), 

    It's got a wrong count somehow.  I suspect that it's not ignoring the first value, it's adding an additional count.  Can you try changing it like below to see if it alters the results?

        Median(
            Descendants([Sample].[SampleID].currentmember),
            [Measures].Value
            ), 

    Thank you. I will give it a shot....why it gives a wrong count...weird...

    Thursday, March 21, 2013 7:22 PM
  • Can you post the MDX?

    I want to choose the median value figure over all Samples and also can slice the figures based on different dimensions. 

    In this case, MDX should be applied to Sample members, right?

        Median(
            [Sample].[SampleID].members,
            [Measures].Value
            ), 

    It's got a wrong count somehow.  I suspect that it's not ignoring the first value, it's adding an additional count.  Can you try changing it like below to see if it alters the results?

        Median(
            Descendants([Sample].[SampleID].currentmember),
            [Measures].Value
            ), 

    Thank you. I will give it a shot....why it gives a wrong count...weird...

    Just tried. Unfortunately it is the same...
    Thursday, March 21, 2013 7:26 PM
  • Hi Bendare again.

    I have following the recommendations here, and the problem is solved. 

    Thanks for your headsup.

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/e32e4145-85f1-4cd8-9891-efc4538732b0/

    Thursday, March 21, 2013 10:19 PM
  • I realize this a getting old, but I've got the exact same problem you did (first datapoint is ignored) , and I can't figure out how to fix it.  How exactly did you correct this?

    The web page you referred to doesn't seem to give the answer.


    Wednesday, July 24, 2013 9:44 PM
  • Hi John

    just try this code. It works for me.

    THIS =

    MEDIAN(

        Existing(sample.sampleID.MEMBERS),

        [Measures].[Value]

    );

    Thursday, July 25, 2013 7:41 AM
  • Thanks very much for the response, Jeffrey.  I appreciate it.

    That's what I'm using, but still not working.  I must be missing something.

    Thanks again.


    EDIT - Finally figured it out.  The "ALL" member was being included in the MEDIAN, effectively adding an element to the list.  Instead of "EXISTING(sample.sampleID.MEMBERS)", I used "EXISTING(sample.sampleID.sampleID.MEMBERS)" which excludes the "ALL" member.
    • Edited by John Ralphson Monday, July 29, 2013 5:06 PM Found solution.
    Friday, July 26, 2013 4:55 PM