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

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
kcantor
Community Champion
Community Champion

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 Super User!




View solution in original post

25 REPLIES 25

@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])


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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

Measures...

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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
Community Champion
Community Champion

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 Super User!




Anonymous
Not applicable

Hi @kcantor , Do you have any example for this?

I am really stuck in filtering highest sum of sales by each person in each category. Please see the below table. I want to fitler Highest sales name in each group. Could you please help to find out this? Thank you in advance.

 

GroupPersonSales
Radimetrics™Christine Shaw5
Radimetrics™Jeffrey Evans2
Radimetrics™Jennifer Stewart2
Radimetrics™Monique Rowley3
Radimetrics™Rachael Viner3
Radimetrics™Ryan Reeve12
Radimetrics™Sean McMahan3
Radimetrics™Tara Larrea4
Radimetrics™Timothy Grobe1
OthersJennifer Stewart9
OthersJohn Collado7
OthersJohn Ferriter1
OthersJohn Pace67
OthersJulie Springer11
OthersKaren Broyles4
OthersKeith Patterson1
OthersRyan Reeve10
OthersSean Casey5
Medrad®Ryan Reeve1
Medrad®Sean McMahan8
Medrad®Tara Larrea1
Medrad®Vanessa Deleon2
GadavistCheryl Mccleary-Bowser2
GadavistCynthia Powers1
GadavistDoug Blaheta2
GadavistLianne Pompeo5
GadavistMaritzia Zilles4
GadavistRobert Kilkelly1

 

kcantor
Community Champion
Community Champion

@Anonymous

If you go back to my original calculation from page 1 on this thread you can set the visual level filter to rank of 1 and then put your groups on the rows. It should work with this type of data as well to give you the top name for each group. If you need more than 1 name, you can filter to how ever many you need.





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

Proud to be a Super User!




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

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

kcantor
Community Champion
Community Champion

Add the RankX measure to your data set.   For my RankX I chose to  Rank my sales performance based upon the YOY dollar variance using: Rank Top $ Variance TotalSales = RANKX(ALL(SalesPerformance[MfgName]),[$ Variance TotalSales])  This is my measure using my data measure and column names.  this returns a value as for where the manufacture falls in the list of all manufacturers. For example, our top performer is 1. Our worst performer is 504 or such.

When I create my report, I add in the information that I want to see without the RankX measure. Then, when the report is ready, I drag my RankX measure into th report filter area. Since i want the top 25 performers, I choose the value to be "less than" and fill in 26 and apply. This reduces the report down to the top 25 performers and is done completely by stand alone measures and built in filter systems.

Please note that I have shared my actual measure specific to my data. You may need to make changes in order to use it for your report.





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

Proud to be a Super User!




I've tried this but can't seem to make it work... would really appreciate it if you could take a look.

 

I have an Article table containing a Description field and a ArticleMonth table containing a Sales field.
I want to see the top 200 articles based on sales.

 

It seems like I have to use RANKX in a similar way but I can't seem to display the data correctly.
Top 200 Articles = RANKX( ALL( Article[Description] ), ArticleMonth[Sales] ) OR
Top 200 Articles = RANKX( ALL( Article[Description] ), CALCULATE( SUM( ArticleMonth[Sales] ) ) )

Trying to display the earlier mentioned fields combined with some others in a Matrix view.

What could I be doing wrong?

MikeZai
Frequent Visitor

Thanks for the TopN explanation using RankX, this worked well for me.  I’m trying to create a table that dynamically selects the Top 5 states by N count but then puts the remaining 45 states into an ‘Other’ category.  Any idea how to do this?  Thanks!

 

State

N

FL

370

CA

173

NY

122

NJ

90

IL

89

Other

1093

 

It worked finally! Thanks a lot.

sallen
Frequent Visitor

Hey guys, I'm really struggling to get my head around the RANKX and TOPN functions, so hoping someone can help.

 

I have a table that shows all of our maintenance jobs that are in progress, and a column in that showing how many days overdue they are (where -2 means there are two days to complete the job and +2 means it is 2 days overdue).

 

All I want to do is create a table in my report that shows the Top 15 overdue jobs and I cannot for the life of me figure this out. I have tried creating measures and all sorts of things but I think that might be a bit redundant as each job only appears once in the report. So basically I want my formula to:

 

Rank all jobs in [Jobs in progress table] by [days overdue column]. Then I'll use that measure in the table and filter to exclude anything >15.

 

The measure I've created looks like this:

 

m_Top15OD = RANKX(ALL('2CSDX 03 004 Open Faults'[Child Case]),SUM('2CSDX 03 004 NAB Open Faults'[No of Days Overdue]))

 

When I put this into a table, the result is always a 1.

 

Any help would be greatly appreciated. I can't beleive how complicated it is to do simple things in Power BI ='(.

 

 

Sean
Community Champion
Community Champion

@sallen It looks like you need a CALCULATE. This should fix it - let me know...

 

m_Top15OD =
RANKX (
    ALL ( '2CSDX 03 004 Open Faults'[Child Case] ),
    CALCULATE ( SUM ( '2CSDX 03 004 NAB Open Faults'[No of Days Overdue] ) )
)
sallen
Frequent Visitor

Unreal, this worked =), thanks so much. Would you be able to explain why the calculate function was required? How is that different to just having SUM?

Sean
Community Champion
Community Champion

without CALCULATE the filter context for each row is always the same - all items have the same rank 1 this way

 

CALCULATE performs the context transition for each row of the table

sallen
Frequent Visitor

Thanks Sean. So effectively, without CALCULATE the measure is looking at each row individually, and ranking it #1. Where calculate forces the measure to look at the whole data set?

sallen
Frequent Visitor

I have some weird behaviour as a result of this measure. It works perfectly, and I have a table in my report that shows:

- Job ID (unique identifier)

- Days overdue

- Rank (with 1 being the most overdue)

 

This all works fine, and my rank goes from 1 up to 15. However, when I add to the table the Case Description (a short text description), all of a sudden the Rank calculation reverts to all 1s again. Anyone able to explain why this would happen?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors