RANKX for Top 5 Facilities by number of successes

[shelbsassy]

I am new to Power BI and I have looked and tried everything to get the Rankx function to try and display the top 5 nursing facilities by the number of success counts for each. 

Basically I have (Sheet1[Nursing Facility]) which is what I am trying to dsplay the top 5 of

Then I have (Sheet1[Success Count] which is a calculated column that is: Success Count = IF(Sheet1[status_id]=1||Sheet1[status_id]=4,1,0)

How can I get the top 5 facilities displayed like this:

Frontier Health and Rehab            9

St Louis Place Health & Rehab     4

Autumn Terrace Health                3

Lees Summit Pointe Health          3

Riverbend Heights                       2

Thanks for any help!

[shelbsassy]

WOW Thank you so much!  That worked perfectly!!  Appreciate your fast response 🙂

[v-haibl-msft]

@shelbsassy

If your problem has been solved, you could mark the right answer as solution to close this thread. 

Best Regards,

Herbert

[Sean · Accepted solution]

@shelbsassy It sounds like you want to RANK the Nursing Facilites by the SUM of the results in the Success Count Column?

If so -  I would suggest:

1) Create a SUM Measure

Success SUM Measure = SUM ( Sheet1[Success Count] )

2) Then create your Ranking Measure

RANK Facilities = RANKX ( ALL(Sheet1[Nursing Facility]), [Success SUM Measure])

NOTE => If you combine steps 1 and 2 you would HAVE TO wrap the sum in calculate

RANK Facilities 2 = RANKX ( ALL(Sheet1[Nursing Facility]), CALCULATE ( SUM (Sheet1[Success Count]) ) )

3) Finally go in the Visual Level Filters => select RANK Facilities => Show items when the value is: less than or equal to 5

Hope this helps!

Next step would be to determine what measure you want to use to break the ties?

EDIT: Forgot to wrap the whole thing in IF ( HASONEVALUE => so you don't rank the total row!

RANK Facilities =
IF (
    HASONEVALUE ( Sheet1[Nursing Facility] ),
    RANKX ( ALL ( Sheet1[Nursing Facility] ), [Success SUM Measure] )
)

[Greg_Deckler]

Is the number in your table the Success Count? I do not see how you could get a Success Count like that from the formula you are providing for Success Count. It seems like it would either be 1 or 0. Or are you saying that you have multiple rows of the same facility and essentially want to summarize them in a table? 

I *think* if it is the later, you might have to use something like SUMMARIZE.

Actually, take a look at this article:

http://www.powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/

And take a look at the section "Ranking by Non Unique Columns"