cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dstiefe Frequent Visitor
Frequent Visitor

Show top n entries

How can i show the top 10 items by a measure (like sales) ?

10 REPLIES 10
Super User
Super User

Re: Show top n entries

Use the RANK or RANKX function as specified here based on your sales number:

https://msdn.microsoft.com/en-us/library/gg492169.aspx

Then in your pivot table/Power View table put a filter on that Rank is <= 10.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


racheldyer Frequent Visitor
Frequent Visitor

Re: Show top n entries

When I use the above RANKX solution on Desktop and put it in a column chart, it works fine until I add a legend. The filter on ranking goes away.  Any idea if Desktop will have some built-in Top N functionality ike Pivot tables do?

 

ALeef Member
Member

Re: Show top n entries

Leiterfluid New Member
New Member

Re: Show top n entries

Unfortunately, using either RANKX or TOPN are overcomplicated means to solve the problem.  IWs should not have to create new measures every time they want to scope or limit a result set to limited number.

 

RANKX has proven useless for me in a situation where I have over 200,000 rows in my dimension table against a 600,000 row fact table. Even a beefy Alienware laptop with 32 GB RAM could not process the measure.

 

This should be part of not just the standard table or chart functions (as it is in excel), but would be useful in Q&A scenarios where I could type in "Top 25 cities by gross revenue" to give me a scoped result set.

 

HarrisMalik Established Member
Established Member

Re: Show top n entries

@Leiterfluid Please support the idea here if you want to see this feature.

fchukoskie Regular Visitor
Regular Visitor

Re: Show top n entries


@Leiterfluid wrote:

... 

RANKX has proven useless for me in a situation where I have over 200,000 rows in my dimension table against a 600,000 row fact table. Even a beefy Alienware laptop with 32 GB RAM could not process the measure.

 

...

 


For a bit, I thought maybe it was just me. Misery loves company, though. Thanks!

cuongle Member
Member

Re: Show top n entries

RANKX surely work in "Import Data" mode, but power Bi Desktop prevents to use it in "Direct Query" mode, does anyone has solution for Direct Query

Highlighted
merago Frequent Visitor
Frequent Visitor

Re: Show top n entries

How do i display rank  number if measure value as Zero "0" .

SamLester Established Member
Established Member

Re: Show top n entries

Quick update: Top N filters were just added in the October 2016 release.

 

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/#analytics

 

Thanks,
Sam Lester (MSFT)