none
MDX TopCount - How do I include the totals for each level in the hierarchy?

    Question

  • This is an MDX statement against the AdventureWorks cube. It lists the top Sales Territory Groups, their regions and the total for the region employing the use of TOPCOUNT to get maximum of top 6 groups and for each group a maximum of three regions. The output measure (Internet Sales Amount) is for the region. How to I include the group's total along with the region's total?  This translates to a real world problem that I would like to solve. I can do it in T-SQL but would prefer to use MDX.

    WITH

    SET [Group] AS

    EXCEPT(       EXCEPT([Sales Territory].[Sales Territory Group].MEMBERS,[Sales Territory].[Sales Territory Group].[All]),[Sales Territory].[Sales Territory Group].[NA])

    SET [Region] AS

    EXCEPT(EXCEPT([Sales Territory].[Sales Territory Region].MEMBERS,[Sales Territory].[Sales Territory Region].[All]),[Sales Territory].[Sales Territory Region].[NA])

    SELECT

    NON EMPTY [Measures].[Internet Sales Amount] ON 0 ,

    NON EMPTY Generate(TOPCOUNT([Group], 6,[Measures].[Internet Sales Amount]),

                               TOPCOUNT([Sales Territory].[Sales Territory Group].CurrentMember * [Region], 3,[Internet Sales Amount])) ON 1

    FROM [Adventure Works]

    WHERE [Date].[Calendar].[Calendar Year].[CY 2008]

    I want to include and repeat the totals for the groups (North America, Europe and Pacific) along with their territory totals.

    Output:

                                    Internet Sales Amount

    North America  Southwest          $2,020,796.06

    North America  Northwest          $1,291,994.33

    North America  Canada $673,628.21

    Europe United Kingdom               $1,210,286.27

    Europe Germany             $1,076,890.77

    Europe France  $922,179.04

    Pacific   Australia              $2,563,884.29


    Brad Chapman

    Thursday, June 27, 2013 4:09 PM

Answers

  • is this what you had in mind...subtotal for top 3 regions in each group?

    WITH 
    	SET [Group] AS
    	  Except (
    		Except ( [Sales Territory].[Sales Territory Group].Members, [Sales Territory].[Sales Territory Group].[All] ),
    		[Sales Territory].[Sales Territory Group].[NA]
    	  )
    	SET [Region] AS
    	  Except (
    		Except ( [Sales Territory].[Sales Territory Region].Members, [Sales Territory].[Sales Territory Region].[All] ),
    		[Sales Territory].[Sales Territory Region].[NA]
    	  )
    	MEMBER [Measures].[Internet Sales Amount Region] AS
    		[Measures].[Internet Sales Amount]
    		,FORMAT_STRING = "Currency"
    	MEMBER [Measures].[Internet Sales Amount Group] AS
    		SUM(
    			(TopCount ( [Sales Territory].[Sales Territory Group].CurrentMember * [Region], 3, [Internet Sales Amount] )),
    			[Measures].[Internet Sales Amount]
    		)
    		,FORMAT_STRING = "Currency"
    SELECT 
    	NON EMPTY {
    		[Measures].[Internet Sales Amount Region],
    		[Measures].[Internet Sales Amount Group]
    	} ON 0,
    	NON EMPTY Generate (
    		  TopCount ( [Group], 3, [Measures].[Internet Sales Amount] ),
    		  TopCount ( [Sales Territory].[Sales Territory Group].CurrentMember * [Region], 3, [Internet Sales Amount] )
    	) ON 1
    FROM 
    	[Adventure Works]
    WHERE 
    	[Date].[Calendar].[Calendar Year].[CY 2008]


    BI Developer and lover of data (Blog | Twitter)

    • Marked as answer by Brad Chapman Monday, July 01, 2013 12:00 AM
    Sunday, June 30, 2013 11:55 PM

All replies

  • Hi Brad,

    Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

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

    Best Regards,


    Elvis Long
    TechNet Community Support

    Sunday, June 30, 2013 5:09 AM
  • is this what you had in mind...subtotal for top 3 regions in each group?

    WITH 
    	SET [Group] AS
    	  Except (
    		Except ( [Sales Territory].[Sales Territory Group].Members, [Sales Territory].[Sales Territory Group].[All] ),
    		[Sales Territory].[Sales Territory Group].[NA]
    	  )
    	SET [Region] AS
    	  Except (
    		Except ( [Sales Territory].[Sales Territory Region].Members, [Sales Territory].[Sales Territory Region].[All] ),
    		[Sales Territory].[Sales Territory Region].[NA]
    	  )
    	MEMBER [Measures].[Internet Sales Amount Region] AS
    		[Measures].[Internet Sales Amount]
    		,FORMAT_STRING = "Currency"
    	MEMBER [Measures].[Internet Sales Amount Group] AS
    		SUM(
    			(TopCount ( [Sales Territory].[Sales Territory Group].CurrentMember * [Region], 3, [Internet Sales Amount] )),
    			[Measures].[Internet Sales Amount]
    		)
    		,FORMAT_STRING = "Currency"
    SELECT 
    	NON EMPTY {
    		[Measures].[Internet Sales Amount Region],
    		[Measures].[Internet Sales Amount Group]
    	} ON 0,
    	NON EMPTY Generate (
    		  TopCount ( [Group], 3, [Measures].[Internet Sales Amount] ),
    		  TopCount ( [Sales Territory].[Sales Territory Group].CurrentMember * [Region], 3, [Internet Sales Amount] )
    	) ON 1
    FROM 
    	[Adventure Works]
    WHERE 
    	[Date].[Calendar].[Calendar Year].[CY 2008]


    BI Developer and lover of data (Blog | Twitter)

    • Marked as answer by Brad Chapman Monday, July 01, 2013 12:00 AM
    Sunday, June 30, 2013 11:55 PM
  • Absolutely what I was looking for.  Very good!  You made it look so easy.  Thanks so much.

    Brad Chapman

    Monday, July 01, 2013 12:00 AM