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

Advertisements

Get Top N results in DAX

To get Top N results in DAX we can use the TOPN function.

EVALUATE
TOPN (
    10,
    SUMMARIZE (
        'Internet Sales',
        'Product'[Product Name],
        "Sales Amount", 'Internet Sales'[Internet Total Sales]
    ),
    'Internet Sales'[Internet Total Sales]
)
ORDER BY 'Internet Sales'[Internet Total Sales] DESC

A similar MDX query might look like this

SELECT TOPCOUNT(
	[Product].[Product Name].[Product Name],
	10, 
	[Measures].[Internet Total Sales]) 
ON 1,
[Measures].[Internet Total Sales] ON 0
FROM [Internet Sales]

If you want the bottom N results you can write a DAX like this

EVALUATE
TOPN (
    10,
    SUMMARIZE (
        'Internet Sales',
        'Product'[Product Name],
        "Sales Amount", 'Internet Sales'[Internet Total Sales]
    ),
    'Internet Sales'[Internet Total Sales],
    1
)
ORDER BY 'Internet Sales'[Internet Total Sales] ASC

In MDX we replace the TOPCOUNT with the BOTTOMCOUNT. And then we add a filter to avoid empty rows

SELECT BOTTOMCOUNT(
	FILTER(
		[Product].[Product Name].[Product Name], 
		NOT ISEMPTY([Measures].[Internet Total Sales])
		),
	10, 
	[Measures].[Internet Total Sales])
ON 1,
[Measures].[Internet Total Sales] ON 0
FROM [Internet Sales]