# Median Calculation in SSAS Multidimensional 2012

### Question

• The data contains mortgage amounts for every property in US. Now we want to be able to slice and dice it by state or county or sale date or ... , in many dimensions and we want to be able to calculate the median for any set dynamically let's say as users are browsing the data from Excel pivot table. Can this be done efficiently if yes how?

Thank you

Gokhan Varol

Sunday, July 28, 2013 4:52 PM

### All replies

• Hi Gokan,

Generally, we can use the following MDX query to get expected median value:
WITH MEMBER Measures.x AS Median
([Date].[Calendar].CurrentMember.Children
, [Measures].[Reseller Order Quantity]
)
SELECT Measures.x ON 0
,NON EMPTY [Date].[Calendar].[Calendar Quarter]*
[Product].[Product Categories].[Subcategory].members *
[Geography].[Geography].[Country].Members
ON 1

In your case, we can consider create a new calculated measure to check this. Please use the following MDX script to create a new calculated measure:
CREATE MEMBER CURRENTCUBE.[Measures].[MedianOrderQuantity]
AS Median([Date].[Calendar].CurrentMember.Children
,[Measures].[Reseller Order Quantity]),
FORMAT_STRING = "0",
VISIBLE = 1 ,
ASSOCIATED_MEASURE_GROUP = 'Reseller Orders' ;

So, we can directly use the following MDX query to get the median value:
SELECT [Measures].[MedianOrderQuantity] on 0,

NON EMPTY [Date].[Calendar].[Calendar Quarter]*
[Product].[Product Categories].[Subcategory].members *
[Geography].[Geography].[Country].Members

on 1

http://technet.microsoft.com/en-us/library/ms166568.aspx

Best Regards,

Elvis Long
TechNet Community Support

Tuesday, July 30, 2013 12:03 PM
• But how do I assign this to a measure no matter how it's sliced and diced, I do not want to put set names into the median function?

Gokhan Varol

Tuesday, July 30, 2013 2:47 PM