MDX - TopCount plus 'Other' or 'The Rest' by group (over a set of members)

Question

I've got requirement to display top 5 customer sales by customer group, but with other customers sales within the group aggregated as 'Others'. Something similar to this question, but counted separately for each of customer groups.

According to MSDN to perform TopCount, over a set of members you have to use Generate function.

This part works ok:

with 

set [Top5CustomerByGroup] AS
GENERATE
( 
    [Klient].[Grupa Klientow].[Grupa Klientow].ALLMEMBERS,
    TOPCOUNT
    (
        [Klient].[Grupa Klientow].CURRENTMEMBER * [Klient].[Klient].[Klient].MEMBERS
        , 5
        , [Measures].[Przychody ze sprzedazy rzeczywiste wartosc]
    )
)

SELECT 
{ [Measures].[Przychody ze sprzedazy rzeczywiste wartosc]} ON COLUMNS,
{
[Klient].[Grupa Klientow].[Grupa Klientow].ALLMEMBERS * [Klient].[Klient].[All], --for drilldown purposes
[Top5CustomerByGroup]
}
ON ROWS
FROM 
(
  SELECT ({[Data].[Rok].&[2013]} ) ON COLUMNS
      FROM [MyCube]
)

however I've got problem with 'Others' part.

I think I was able to construct set with other customers by group (data looks good) as:

set [OtherCustomersByGroup] AS
GENERATE
( 
    [Klient].[Grupa Klientow].[Grupa Klientow].ALLMEMBERS,
    except
    (
        {[Klient].[Grupa Klientow].CURRENTMEMBER * [Klient].[Klient].[Klient].MEMBERS},
        TOPCOUNT
        (
            [Klient].[Grupa Klientow].CURRENTMEMBER * [Klient].[Klient].[Klient].MEMBERS
            , 5
            , [Measures].[Przychody ze sprzedazy rzeczywiste wartosc]
        )
    )
)

however I don't have idea how to aggregate it with grouping.

Doing this as in this question

member [Klient].[Klient].[tmp] as
aggregate([OtherCustomersByGroup])

produces one value, which is logical.

I think I need list of sets with 'other' customers in each group instead of single [OtherCustomersByGroup] set, but don't have idea how to construct them.

Does anyone have any ideas or suggestions?

UPDATE:

There is some misunderstanding of my needs. I need Top n customers within each of customer group by sales with sales of other customers in this group aggregated to one position (let's say called Others).

For example for this simplified input:

| Group  | Client   | Sales  |
|--------|----------|--------|
| Group1 | Client1  |    300 |
| Group1 | Client2  |      5 |
| Group1 | Client3  |    400 |
| Group1 | Client4  |    150 |
| Group1 | Client5  |    651 |
| Group1 | Client6  | null   |
| Group2 | Client7  |     11 |
| Group2 | Client8  |     52 |
| Group2 | Client9  |     44 |
| Group2 | Client10 |     21 |
| Group2 | Client11 |    201 |
| Group2 | Client12 |    325 |
| Group2 | Client13 |    251 |
| Group3 | Client14 |     15 |

I need such output (here is top 2):

| Group  | Client   | Sales  |
|--------|----------|--------|
| Group1 | Client5  |    651 |
| Group1 | Client3  |    400 |
| Group1 | Others   |    455 |
| Group2 | Client12 |    325 |
| Group2 | Client13 |    251 |
| Group2 | Others   |    329 |
| Group3 | Client14 |     15 |
| Group3 | Others   |  null  | <- optional row

Sorting isn't required, we are going to process it by client side.

Answer 1

The following is against AdvWrks and uses a technique I saw on Chris Webb's blog which he outlines here:
https://cwebbbi.wordpress.com/2007/06/25/advanced-ranking-and-dynamically-generated-named-sets-in-mdx/

The section of the script that creates the set MyMonthsWithEmployeesSets I find very difficult to get my head around - maybe @AlexPeshik could shed a little more light on what is happening in the following script.

WITH 
  SET MyMonths AS 
    TopPercent
    (
      [Date].[Calendar].[Month].MEMBERS
     ,20
     ,[Measures].[Reseller Sales Amount]
    ) 
  SET MyEmployees AS 
    [Employee].[Employee].[Employee].MEMBERS 
  SET MyMonthsWithEmployeesSets AS 
    Generate
    (
      MyMonths
     ,Union
      (
        {[Date].[Calendar].CurrentMember}
       ,StrToSet
        ("
             Intersect({}, 
             {TopCount(MyEmployees, 10, ([Measures].[Reseller Sales Amount],[Date].[Calendar].CurrentMember))
             as EmployeeSet"
            + 
              Cstr(MyMonths.CurrentOrdinal)
          + "})"
        )
      )
    ) 
  MEMBER [Employee].[Employee].[RestOfEmployees] AS 
    Aggregate
    (
      Except
      (
        MyEmployees
       ,StrToSet
        (
          "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
        )
      )
    ) 
  MEMBER [Measures].[EmployeeRank] AS 
    Rank
    (
      [Employee].[Employee].CurrentMember
     ,StrToSet
      (
        "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
      )
    ) 
SELECT 
  {
    [Measures].[EmployeeRank]
   ,[Measures].[Reseller Sales Amount]
  } ON 0
 ,Generate
  (
    Hierarchize(MyMonthsWithEmployeesSets)
   ,
      [Date].[Calendar].CurrentMember
    * 
      {
        Order
        (
          Filter
          (
            MyEmployees
           ,
            [Measures].[EmployeeRank] > 0
          )
         ,[Measures].[Reseller Sales Amount]
         ,BDESC
        )
       ,[Employee].[Employee].[RestOfEmployees]
      }
  ) ON 1
FROM [Adventure Works];

---

Edit - solution for Alex's third attempt:

WITH 
  SET [AllCountries] AS [Country].[Country].MEMBERS 
  SET [AllStates]    AS [State-Province].[State-Province].MEMBERS 
  SET [Top2States] AS 
    Generate
    (
      [AllCountries]
     ,TopCount
      (
        (EXISTING 
          [AllStates])
       ,3
       ,[Measures].[Internet Order Count]
      )
    ) 
  MEMBER [State-Province].[All].[RestOfCountry] AS 
    Aggregate({(EXISTING {[AllStates]} - [Top2States])}) 
SELECT 
  {[Measures].[Internet Order Count]} ON COLUMNS
 ,{
      [AllCountries]
    * 
      {
        [Top2States]
       ,[State-Province].[All].[RestOfCountry]
       ,[State-Province].[All]
      }
  } ON ROWS
FROM [Adventure Works];