CODEWITHSUNDEEP
ssasmdxcubeolap-cube
Aditya
Aditya

Reputation: 2311

How can I select top 25 customers ordered by amount descending, for each dept?

I want top 25 customers ordered by amount descending, for each dept. like:

Dept|Customer|Amt

    1 cust_1 5000
      cust_2 2000
        .
        .
        .
    2 cust_26 6000
      cust_27 3000
        .
        .
    3
    .
    .
    7

What I have tried so far is:

SELECT 
NON EMPTY { [Measures].[Amount] } ON COLUMNS ,

NON EMPTY { 

[Customer].[DEPT].[DEPT]*

ORDER( 

TOPCOUNT([Customer].[Customer].[Customer],25,[Measures].[Amount]),

[Measures].[Amount], DESC)

}

FROM [cube]

I am getting result but not like 25 for each dept, though I have proper data.

Upvotes: 1

Views: 359

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Your second term of the cross join for the rows (ORDER(TOPCOUNT(...))) is not aware of the first ([Customer].[DEPT].[DEPT]). You should use Generate for this, as it loops over the departments, and thus allows you to put the current department into the first argument of the TOPCOUNT:

SELECT 
NON EMPTY { [Measures].[Amount] } ON COLUMNS ,
NON EMPTY
Generate([Customer].[DEPT].[DEPT],
         TOPCOUNT({[Customer].[DEPT].CURRENTMEMBER}
                  *
                  [Customer].[Customer].[Customer],
                  25,
                  [Measures].[Amount]
                 )
        )
FROM [cube]

Also note that you do not need to order the result of TOPCOUNT descending, it is already ordered this way.

Upvotes: 1

Related Questions