Group by a Category and then Rank

[ScotchCat]

Hi - I need assistance with the following - I'm rather new to Power BI so please be gentle! 

I have the following table of data that I would like to Group by the Category column and then create a rank based on the number of occurrences (Accounts) within each Category.   To go further - I will need to create a Pie Chart that shows the Top 3 Categories with the most Accounts.   All help is appreciated! 

Data table is named - CoolSports 

ACCOUNT	CATEGORY	NEED RANKING
123	WaterSki	1
357	WaterSki	1
741	WaterSki	1
713	WaterSki	1
157	WaterSki	1
627	WaterSki	1
987	SnowSki	2
159	SnowSki	2
963	SnowSki	2
379	SnowSki	2
564	Biking	3
753	Biking	3
618	Biking	3
852	Hiking	4
279	Hiking	4
159	Walk	5

[CNENFRNL]

I always avoid using CALCULATE/CALCULATETABLE in a calculated column in order to avoid complexity of context transition.

RANK = 
RANKX(
    CoolSports,
    COUNTROWS(
        FILTER(
            CoolSports,
            CoolSports[CATEGORY] = EARLIER( CoolSports[CATEGORY] )
        )
    ), , ,
    DENSE
)

[AlexisOlson · Accepted solution]

This is certainly a factor to keep in mind but can often result in a less efficient query. In this simple case, the query engine seems to optimize well enough that the following is only marginally faster (tested on a 500k row table), although in general, this sort of approach can make a huge difference.

Rank =
RANKX (
    VALUES ( CoolSports[CATEGORY] ),
    CALCULATE (
        COUNTROWS ( CoolSports ),
        ALLEXCEPT ( CoolSports, CoolSports[CATEGORY] )
    ),
    ,
    DESC
)