title: MDX - TopCount plus 'Other' or 'The Rest' by group (over a set of members)
body: <p>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   <a href="https://stackoverflow.com/questions/787532/mdx-topcount-plus-other-or-the-rest">this question</a>, but counted separately for each of customer groups.</p>
  
  <p>According to <a href="https://msdn.microsoft.com/en-us/library/ms145526.aspx" rel="nofollow noreferrer">MSDN</a> to perform TopCount, over a set of members you have to use Generate function. </p>
  
  <p>This part works ok:</p>
  
  <pre><code>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].&amp;[2013]} ) ON COLUMNS
        FROM [MyCube]
  )
  </code></pre>
  
  <p>however I've got problem with 'Others' part.</p>
  
  <p>I think I was able to construct set with other customers by group (data looks good) as:</p>
  
  <pre><code>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]
          )
      )
  )
  </code></pre>
  
  <p>however I don't have idea how to aggregate it with grouping.</p>
  
  <p>Doing this as in <a href="https://stackoverflow.com/questions/787532/mdx-topcount-plus-other-or-the-rest">this question</a> </p>
  
  <pre><code>member [Klient].[Klient].[tmp] as
  aggregate([OtherCustomersByGroup])
  </code></pre>
  
  <p>produces one value, which is logical. </p>
  
  <p>I think I need list of sets with 'other' customers in each group instead of single <code>[OtherCustomersByGroup]</code> set, but don't have idea how to construct them.</p>
  
  <p>Does anyone have any ideas or suggestions?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>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).</p>
  
  <p>For example for this simplified input:</p>
  
  <pre><code>| 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 |
  </code></pre>
  
  <p>I need such output (here is top 2):</p>
  
  <pre><code>| 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  | &lt;- optional row
  </code></pre>
  
  <p>Sorting isn't required, we are going to process it by client side.</p>