MDX Top Count Sub total

Question

I am trying to create an MDX query to calculate the top selling stores for a territory, then subtotal the territory.

I have used the TOPCOUNT function with the GENERATE function to create a SET for the Top Locations, however I am having real trouble trying to sub total each territory.

My MDX is as follows:

WITH SET [TopLocationsPerTerritory] AS
    GENERATE( 
        Except ([Locations].[Territory].MEMBERS, [Locations].[Territory].[All]),

        TOPCOUNT(
             {[Locations].[Territory].CurrentMember} * Except ([Locations].[Location Hierarchy].[Location].MEMBERS,[Locations].[Location Hierarchy].[Location].[All]),
            5, 
            [Measures].[SLS ($)]
        )
    )
SELECT  {
            [Measures].[SLS YTD ($)],
            [Measures].[SbD BUD SLS YTD ($)],
            [Measures].[SbD BUD v ACT SLS YTD VAR %],
            [Measures].[SLS LFL YTD %],

            [Measures].[SLS GP YTD ($)],
            [Measures].[SbD BUD GP YTD ($)],
            [Measures].[SbD BUD v ACT GP YTD VAR %],
            [Measures].[SLS LFL GP YTD %],

            [Measures].[SLS ($)],
            [Measures].[SbD BUD GP ($)],
            [Measures].[SbD BUD v ACT SLS VAR %],
            [Measures].[SLS LFL %],

            [Measures].[SLS GP ($)],
            [Measures].[SbD BUD GP ($)],
            [Measures].[SbD BUD v ACT GP VAR %],
            [Measures].[SLS LFL GP %]

        } ON COLUMNS,
        (
            [TopLocationsPerTerritory]
        ) on ROWS

And the results are good. enter image description here However I have tried several ways and cannot get a sub total for each territory. I managed to get a aggregate of the whole dataset however that is not what I need.

It's going to very hard for people to help you. Perhaps you could create a simpler version of your dataset with sample data and desired results. — Stephan
In my scenario there was an extra variable that was stopping it from working, so I split my results into a second dataset to overcome this issue. I've marked your response below as the correct answer as I believe it will resolve the issue for others. oh, and thank you very much :) — Will Wainwright

whytheq whytheq · Accepted Answer · 2015-05-05T08:24:45

Try a union with the All member:

WITH 
  SET [TopLocationsPerTerritory] AS 
    Generate
    (
      Except
      (
        [Locations].[Territory].MEMBERS
       ,[Locations].[Territory].[All]
      )
     ,Union
      (
        TopCount
        (
            {[Locations].[Territory].CurrentMember}
          * 
            Except
            (
              [Locations].[Location Hierarchy].[Location].MEMBERS
             ,[Locations].[Location Hierarchy].[Location].[All]
            )
         ,5
         ,[Measures].[SLS ($)]
        )
       ,(
          [Locations].[Territory].CurrentMember
         ,[Locations].[Location Hierarchy].[Location].[All]
        )
      )
    )
...
...
...

Prototyping in AdvWrks the above seems to work fine:

WITH 
  SET [Top5StatesPerCountry] AS 
    Generate
    (
      [Country].[Country].MEMBERS
     ,Union
      (
        TopCount
        (
          [Country].CurrentMember * [State-Province].[State-Province].MEMBERS
         ,5
         ,[Measures].[Internet Order Count]
        )
       ,(
          [Country].CurrentMember
         ,[State-Province].[All]
        )
      )
    ) 
SELECT 
  {[Measures].[Internet Order Count]} ON COLUMNS
 ,{[Top5StatesPerCountry]} ON ROWS
FROM [Adventure Works];

Here is a snippet of the result:

enter image description here

But the following is much simpler solution that does not bother with the union inside generate:

WITH 
  SET [Top5StatesPerCountry] AS 
    Generate
    (
      [Country].[Country].MEMBERS
     ,TopCount
      (
        (EXISTING 
          [State-Province].[State-Province].MEMBERS)
       ,5
       ,[Measures].[Internet Order Count]
      )
    ) 
SELECT 
  {[Measures].[Internet Order Count]} ON COLUMNS
 ,
    [Country].[Country].MEMBERS
  * 
    {
      [Top5StatesPerCountry]
     ,[State-Province].[All]
    } ON ROWS
FROM [Adventure Works];

Edit

You could amend the script immediately above to this:

WITH 
  SET [Top5StatesPerCountry] AS 
    Generate
    (
      [Country].[Country].MEMBERS
     ,TopCount
      (
        (EXISTING 
          [State-Province].[State-Province].MEMBERS)
       ,5
       ,[Measures].[Internet Order Count]
      )
    ) 
  MEMBER [State-Province].[State-Province].[AGGREGTOP5] AS 
    AGGREGATE(EXISTING [Top5StatesPerCountry])
SELECT 
  {[Measures].[Internet Order Count]} ON COLUMNS
 ,
    [Country].[Country].MEMBERS
  * 
    {
      [Top5StatesPerCountry]
     ,[State-Province].[All]
     ,[State-Province].[State-Province].[AGGREGTOP5]
    } ON ROWS
FROM [Adventure Works];

MDX Top Count Sub total

1 Answers