Answered by:
Median() function doesn't work as expected.

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?
Question
Answers

Hi Bendare again.
I have following the recommendations here, and the problem is solved.
Thanks for your headsup.
http://social.msdn.microsoft.com/Forums/enUS/sqlanalysisservices/thread/e32e414585f14cd89891efc4538732b0/
 Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Thursday, March 28, 2013 2:47 AM
All replies


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

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
), 
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.

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.

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
), 
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...

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...

Hi Bendare again.
I have following the recommendations here, and the problem is solved.
Thanks for your headsup.
http://social.msdn.microsoft.com/Forums/enUS/sqlanalysisservices/thread/e32e414585f14cd89891efc4538732b0/
 Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Thursday, March 28, 2013 2:47 AM

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.
 Edited by John Ralphson Wednesday, July 24, 2013 9:45 PM type


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.