Calculating Median In OLAP

[abhishek.thakur]

I am facing some problem in calculating Median

I am trying to calculate the median value using one of the measures and a dimension value.



Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:



opid time median

1 55

2 23

3 23

Total 23



The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.



What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.



I am using a calculated field Median with the following expression.



MEDIAN

( [Dim Operation].[Dim Operation].currentmember.children ,[Measures].[Elapsed Time])



Thanks

[Island1]

I believe I know what the issue is, but rather than waste time, could you restate this query in the sample Adventure Works / FoodMart 2000 AS DB, and then show what you are getting as opposed to what you want to get?

The reason I ask is that, after spending time in guessing about your dim structure and proposing a solution, I may next hear about a level or hierarchical consideration that is not apparent in the very skeletal information you provide in the original question. (I can't count the times this has happened in the past ...)

I look forward to helping you further ...

Bill