Top products within each category in MDX

If you want to have top products for each model this is a simple way of doing that in MDX.

SELECT [Measures].[Internet Total Sales] ON COLUMNS,
NON EMPTY Generate (
Order (
{ [Product].[Category].[Model].Members },
[Measures].[Internet Total Sales],
ASC
),
Crossjoin (
[Product].[Category].CurrentMember,
TopCount (
[Product].[Product Name].Children,
2,
( [Measures].[Internet Total Sales] )
)
)
) ON ROWS
FROM [Internet Sales]

The result will look like this

Top products within each category

If you want to have top 2 models and then top 5 products you can write a query like this

WITH SET TopModels AS
TopCount (
{ [Product].[Category].[Model] },
2,
( [Measures].[Internet Total Sales] )
)
SELECT [Measures].[Internet Total Sales] ON COLUMNS,
Generate (
{ TopModels },
Crossjoin (
{ [Product].[Category].CurrentMember },
TopCount (
[Product].[Product Name].Children,
5,
( [Measures].[Internet Total Sales] )
)
)
) ON ROWS
FROM [Internet Sales]

And the result will look like this:

Top products within each category 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Facebook photo

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

Connecting to %s