cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joschultz Member
Member

Top 15 in a report

What is the easiest way to show just the top 15 items in a report?  Example is I want to show the top 15 products in sales for the day.  I will have hundreds of products in the data but only want the top 15.

 

Thank you,

 

Joseph

1 ACCEPTED SOLUTION

Accepted Solutions
kcantor Super Contributor
Super Contributor

Re: Top 15 in a report

I use the Rank X measure for my products in the filter area of the visual (or whole page as needed). Then, when I add other values, there is nothing to break. I don't put the Rank X in the visual, I just use it as a filter for the page/report. In fact, I usually fill in all other information then apply it last to pull up my top 25.



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

Proud to be a Datanaut!




View solution in original post

25 REPLIES 25
spiderrob Member
Member

Re: Top 15 in a report

Use RANKX in a measure to get rankings numbers for the particular value and then drop the measure in one of the filter areas and apply less than or equal to 15 or whatever top x you are after.

 

 

Super User
Super User

Re: Top 15 in a report

@joschultz 

@JenUnderwood just shared this solution in a different forum. You can accomplish this by building a set of dax measures which allows you to accomplish this.

Build the measures, and use product and (Units Sold Top 15 Product) in your visual and it will automatically select the top 15 products based on sales numbers (or whatever you want to use in the measure to calculate by)

 

Example:

Units Sold Total = SUM(table[Product Sales])
Units Sold Rank by Product = RANKX(ALLSELECTED(table[Product]), [Units Sold Total])
Units Sold Top 15 Product = IF([Units Sold Rank by Product] <= 15, [Units Sold Total])


Near SE WI? Join our PUG Milwaukee Brew City PUG
joschultz Member
Member

Re: Top 15 in a report

Do I build them as measures or fields?  I tried measures but I wasn't able to get it to work.

Super User
Super User

Re: Top 15 in a report

Measures...

What is not working? the measures, or the visuals?


Near SE WI? Join our PUG Milwaukee Brew City PUG
ALeef Member
Member

Re: Top 15 in a report

joschultz Member
Member

Re: Top 15 in a report

So I have gotten this to work but when I add another column to show the number of units it breaks my formula.  Is it possible to rank by sales and then also show units?

kcantor Super Contributor
Super Contributor

Re: Top 15 in a report

I use the Rank X measure for my products in the filter area of the visual (or whole page as needed). Then, when I add other values, there is nothing to break. I don't put the Rank X in the visual, I just use it as a filter for the page/report. In fact, I usually fill in all other information then apply it last to pull up my top 25.



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

Proud to be a Datanaut!




View solution in original post

Highlighted
joschultz Member
Member

Re: Top 15 in a report

That worked perfectly!!!!   Thank you for suggesting it. 

pbi2015 Frequent Visitor
Frequent Visitor

Re: Top 15 in a report

Can you explain better the solution of how to calculate the TOP N in a report using RANKX ?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 188 members 1,804 guests
Please welcome our newest community members: