Getting breakdown of "Others" (the rest of Top N members) with SSAS MDX

Question

How can I recursively get the breakdown of "Others" when Top N is applied to dimensions?

Imagine a measure Sales Amount is sliced by 3 dimensions, Region, Category and Product, and Top 1 is applied to each dimension. The result I want to see is a table like below. On each slice, the rest of members are grouped as "Others".

 Region | Category | Product        | Sales
============================================
 Europe | Bikes    | Mountain Bikes | $100
        |          |------------------------
        |          | Others         | $ 30
        |-----------------------------------
        | Others   | Gloves         | $ 50
        |          |------------------------
        |          | Others         | $120
--------------------------------------------
 Others | Clothes  | Jackets        | $ 80
        |          |------------------------
        |          | Others         | $130
        |-----------------------------------
        | Others   | Shoes          | $ 90
        |          |------------------------
        |          | Others         | $110
--------------------------------------------

When an "Others" appears, I want to see the Top 1 of the next dimension within the scope of this "Others". This seems a little tricky. e.g. tuples like (North America, Clothes) and (Central America, Clothes) need to be aggregated as (Other Regions, Clothes). Is there a neat way to aggregate the measure based on the 2nd dimension, Category?

Alternatively, I think a sub cube that filters out Europe will easily provide the breakdown of Other Regions, Clothes and Other Categories. However, this is likely to result in creating many dependent queries. For an easy processing of the result set, it would be ideal if the query returns data in the above format.

Can this be possibly achieved by a single MDX query?

BOUBRIT Nacim BOUBRIT Nacim · Accepted Answer · 2020-10-08T01:17:52

To get the breakdown of others we must use dynamic set, EXCEPT() and aggregate functions

in each of the three dimensions we will need to create a named dynamic set that holds too members (top 1 and others ).

as exemple, in the dimension category i have created a dynamic set that holds two members (Top 1 and others) like this :

CREATE MEMBER 
CURRENTCUBE.[Product].[French Product Category Name].[ALL].[OTHERS] AS 
AGGREGATE(EXCEPT([Product].[French Product Category Name].[French Product Category Name].MEMBERS,
TOPCOUNT([Product].[French Product Category Name].[French Product Category Name],1,[Measures].[Sales Amount])
));

CREATE DYNAMIC SET [TOP1 and Others] 
AS {TOPCOUNT([Product].[French Product Category Name].[French Product Category Name],1,[Measures].[Sales Amount]),[OTHERS]};

because the set is dynamic then the values of top 1 and others will change according to the filters and slicers that you applay.

Getting breakdown of "Others" (the rest of Top N members) with SSAS MDX

1 Answers