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]

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 )

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