none
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
    FROM [Adventure Works]

    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
    From [Adventure Works]

    For more information about create a calculated measure, please refer to the following article:
    http://technet.microsoft.com/en-us/library/ms166568.aspx

    If you have any feedback on our support, please click here.

    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