none
MDX Average over specific dimensions RRS feed

  • Question

  • I would like to create a calculated member that takes an average of a measure across a specific set of dimensions.
    At the moment I am trying to just write the MDX to see if I can get it to return the proper data:

    select
    { avg(
    	{[Date Dimension].[Year],
    	 [Date Dimension].[Week Number],
    	 [Location Dimension].[Location Name],
         [Worker Dimension].[Worker Name]},
         [Measures].[Rank]),
    	[Location Dimension].[Location Name]
    } on columns,
    {
      [Worker Dimension].[Worker Name]
    } on rows
    from [dbcube]
    The idea is to produce a weekly report that will give each worker's rank for the week for each location, and also display their average rank.
    Thursday, April 23, 2009 4:51 PM

All replies

  • Take a look at this query written against Adventure Works.  Let me know if this is the direction you are wanting to go, and if it is I can walk you through it:

    with
    member

     

    [Measures].[Rank In Period] as
        RANK(
            ([Product].[Subcategory].
    CurrentMember, [Date].[Calendar].CurrentMember),
            ORDER(
                [Product].[Subcategory].[Subcategory].
    Members * [Date].[Calendar].CurrentMember,
                ([Measures].[Reseller Sales Amount]),
                BDESC
                )
            )
    member [Measures].[Average Rank Over Periods] as
        AVG(
            [Product].[Subcategory].
    CurrentMember *
                EXISTS([Date].[Calendar].[Month].Members, [Date].[Calendar].[CY 2003]),
            [Measures].[Rank In Period]
            )
        ,FORMAT="Standard"
    SELECT
        {[Measures].[Reseller Sales Amount], [Measures].[Rank In Period], [Measures].[Average Rank Over Periods]} ON COLUMNS,
        NONEMPTY(
            EXISTS([Date].[Calendar].[Month].Members, [Date].[Calendar].[CY 2003]) *
                [Product].[Subcategory].[Subcategory].
    Members,
            [Measures].[Reseller Sales Amount]
            )
    ON ROWS
    FROM
     [Adventure Works]

    HTHs,
    Bryan


    Hitachi Consulting -- Inspiring your next success! ®
    Thursday, April 23, 2009 6:25 PM
    Answerer
  • I already have the rank as a member in the measures group, so I wouldn't need the ranking part, btu I think the rest will do for me... I am a little lost on what I am looking at however.

    Here is how this seems to translate to me... however this just produces "#Error" on all cells:
    with member [Measures].[Rank Over Period] as
     avg(
    	{[Worker Dimension].[Worker Name].CurrentMember,
    	[Date Dimension].[Year].ALLMEMBERS,
    	[Date Dimension].[Week Number].ALLMEMBERS},
    	[Measures].[Rank]
    	)
    select
     {
    	(([Date Dimension].[Year].[2009],
    	[Date Dimension].[Week Number].[15]),
    	[Location Dimension].[Location Name].ALLMEMBERS)
     } on columns,
     {
    	[Worker Dimension].[Worker Name].ALLMEMBERS
     } on rows
    from [dbcube]
    where [Measures].[Rank Over Period]
    I realize that I am probably really far off, but I am not really sure what to do here...
    Thursday, April 23, 2009 7:29 PM
  • I think I am a lot closer to what I want, however to do it I had to create a week-year attribute in the date dimension.

    with member [Measures].[Avg Rank] as
    	avg({
    		([Date Dimension].[Week Year].[01-2007]:
    			[Date Dimension].[Week Year].[12-2009])},
    		[Measures].[Rank]
    		)
    select
     {
    	[Location Dimension].[Location Name].ALLMEMBERS	
     } on columns,
     {
    	[Worker Dimension].[Worker Name].ALLMEMBERS
     } on rows
    from [dbcube]
    where [Measures].[Avg Rank]
    
    

    I was trying to toss in the year and the week sets in the average function to get it to roll up the average how I wanted, but that was not working...  Is this the proper way to do this?
    Thursday, April 23, 2009 8:59 PM