Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
Proud to be a Super User!
@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])
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?
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?
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.
Proud to be a Super User!
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.
Group | Person | Sales |
Radimetrics™ | Christine Shaw | 5 |
Radimetrics™ | Jeffrey Evans | 2 |
Radimetrics™ | Jennifer Stewart | 2 |
Radimetrics™ | Monique Rowley | 3 |
Radimetrics™ | Rachael Viner | 3 |
Radimetrics™ | Ryan Reeve | 12 |
Radimetrics™ | Sean McMahan | 3 |
Radimetrics™ | Tara Larrea | 4 |
Radimetrics™ | Timothy Grobe | 1 |
Others | Jennifer Stewart | 9 |
Others | John Collado | 7 |
Others | John Ferriter | 1 |
Others | John Pace | 67 |
Others | Julie Springer | 11 |
Others | Karen Broyles | 4 |
Others | Keith Patterson | 1 |
Others | Ryan Reeve | 10 |
Others | Sean Casey | 5 |
Medrad® | Ryan Reeve | 1 |
Medrad® | Sean McMahan | 8 |
Medrad® | Tara Larrea | 1 |
Medrad® | Vanessa Deleon | 2 |
Gadavist | Cheryl Mccleary-Bowser | 2 |
Gadavist | Cynthia Powers | 1 |
Gadavist | Doug Blaheta | 2 |
Gadavist | Lianne Pompeo | 5 |
Gadavist | Maritzia Zilles | 4 |
Gadavist | Robert Kilkelly | 1 |
@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.
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 ?
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.
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?
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.
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 ='(.
@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] ) ) )
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?
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
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?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |