MDX #2 – Top (n) Products Sales in each Sales Territory

To get top (n) products with highest sales in every sales territory, the GENERATE function is exactly what we need which applies a set to each member of another set.

SELECT
	{[Measures].[Reseller Sales Amount]} ON Columns,
	GENERATE(
		[Sales Territory].[Sales Territory].[Country].MEMBERS
		,TOPCOUNT([Sales Territory].[Sales Territory].CURRENTMEMBER
			*[Product].[Product].[Product].MEMBERS
			,5
			,[Measures].[Reseller Sales Amount]
		)
	) ON ROWS
FROM [Adventure Works]

MDX03

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s