CODEWITHSUNDEEP
ssasmdxolap-cube
sagar pant
sagar pant

Reputation: 357

MDX: Top X by two groupings and Top Y (X>Y) by one of the two groups

I am trying to write a query that shows TOP 4 Internet Sales Amount based on year and product. And, at the same time, I am trying to show TOP 1 Reseller Sales Amount based on year only. 

Year        Product          Internet Sales Amount      Reseller Sales Amount
CY 2015   Road-150 Red, 48        $153,215                     $25,021
CY 2015   Road-150 Red, 68        $101,215                     $25,021
CY 2015   Road-150 Red, 22         $93,215                     $25,021
CY 2015   Road-250 Red, 52         $90,215                     $25,021
CY 2014   Road-150 Red, 100       $202,215                     $60,021
CY 2014   Road-220 Black, 90      $180,101                     $60,021
CY 2014   Road-65 Blue, 28        $139,465                     $60,021
CY 2014   Road-86 Red, 33         $100,001                     $60,021

I wrote a following query to achieve the Top 4 Internet Sales Amount by Year and Product, but couldn't figure out how to add the next measure to the resultset. 

WITH 
  SET [TopSalesbyYearAndProd] AS 
    Generate
    (
      [Date].[Calendar Year].[Calendar Year].MEMBERS
     ,TopCount
      (
          [Date].[Calendar Year].CurrentMember
        * 
          [Product].[Product].[Product].MEMBERS
       ,4
       ,[Measures].[Internet Sales Amount]
      )
    ) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON 0
 ,NON EMPTY 
    {[TopSalesbyYearAndProd]} ON 1
FROM [Adventure Works];

Upvotes: 1

Views: 55

Answers (1)

whytheq
whytheq

Reputation: 35597

This seems to work - there is probably a much more elegant way but I've not found it:

WITH 
  SET [TopSalesbyYearAndProd] AS 
    Generate
    (
      [Date].[Calendar Year].[Calendar Year].MEMBERS
     ,TopCount
      (
          [Date].[Calendar Year].CurrentMember
        * 
          [Product].[Product].[Product].MEMBERS
       ,4
       ,[Measures].[Internet Sales Amount]
      )
    ) 
  MEMBER [Measures].[TopResellerAmount] AS 
    (
      Generate
      (
        {[Date].[Calendar Year].CurrentMember}
       ,TopCount
        (
            [Date].[Calendar Year].CurrentMember
          * 
            [Product].[Product].[Product].MEMBERS
         ,1
         ,[Measures].[Reseller Sales Amount]
        )
      ).Item(0).Item(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[TopResellerAmount]
  } ON 0
 ,NON EMPTY 
    {[TopSalesbyYearAndProd]} ON 1
FROM [Adventure Works];

Upvotes: 1

Related Questions