When I run the query below I only get 2 records back. If I drop the TOPCOUNT, I get 508 records.
Why is it not giving me the top 5 from the 508 records? What am I missing?
Regs
Jon
with MEMBER Measures.[EmailCount] as IIF(ISEMPTY([Measures].[Tran Count]), 0 ,[Measures].[Tran Count]) MEMBER Measures.[IncomePerEmail] as [Measures].[Amount]/ IIF(Measures.[EmailCount] = 0, 1 , Measures.[EmailCount] ) MEMBER Measures.[Income Range] as CASE WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members, Measures.[Amount] ) <= 10000 THEN '0-10000' WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members, Measures.[Amount] ) <= 50000 THEN '10001-50000' WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members, Measures.[Amount] ) <= 100000 THEN '50001-100000' WHEN Sum(EXISTING [Dim IFA Details].[Parent Key].[Adviser Group].Members, Measures.[Amount] ) <= 200000 THEN '100001-200000' else '> 200000' end SELECT { [Measures].[Amount] , Measures.[EmailCount], measures.[Income Range], Measures.[IncomePerEmail] } ON COLUMNS, TOPCOUNT( NONEMPTY([Dim IFA Details].[Parent Key].[Adviser Group].Members, Measures.Amount) , 5 , Measures.[IncomePerEmail] ) having Measures.[Income Range] = '10001-50000' on rows FROM [Income and Emails Cube] where [Dim Date].[Fiscal Year].&[FY 13/14]