How do I use a top count so it only returns the first 2 records with the highest sales for each sales territory group (North America, Pacific, etc...)
WITH
MEMBER [Measures].[Group_Ranking_InternetSales] AS
RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
)
MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
RANK(( [Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].CurrentMember),
ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember, [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
)
SELECT
{
[Measures].[Group_Ranking_InternetSales]
,[Measures].[Group_Territory_Ranking_InternetSales]
, [Measures].[Internet Sales Amount]
} ON COLUMNS,
(
ORDER ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC )
,ORDER ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC )
)
ON ROWS
FROM
[Adventure Works]
The end result should be that it removes the highlighted row below.
Try now