Top 15 in a report

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.

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

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?

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 ='(.

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?

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?

What table is the Case Description field in? And how are the tables related?

It's all just in the same table. For some background, I have been using Excel to do reporting for years, and I'm now starting to look into putting everything into Power BI. So I'm very new to tables and things like that. I'm actually wondering if there's some basic online training I could do to help with my understanding.

If its all in the same table that should definitely not happen???

Weird. So this is my table without the case description (everything working fine):

Without case description

And here it with the case description. All I've done to add this to the table is to drag it from the field list into the 'Values' section of the visualisation:

With case description

Thanks so much again for your help, this is really helping me understand how it all works. I think once I can work through a couple of examples I'll be able to teach myself a lot more effectively.

Stuart

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!

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?