Median Calculation in SSAS Multidimensional 2012 RRS feed

  • 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
          , [Measures].[Reseller Order Quantity]
    SELECT Measures.x ON 0
    ,NON EMPTY [Date].[Calendar].[Calendar Quarter]*
       [Product].[Product Categories].[Subcategory].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 *

    on 1
    From [Adventure Works]

    For more information about create a calculated measure, please refer to the following article:

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

    Best Regards,

    Elvis Long
    TechNet Community Support

    • Edited by Elvis Long Tuesday, July 30, 2013 12:04 PM edit
    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