cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScotchCat
Regular Visitor

Group by a Category and then Rank

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 

ACCOUNTCATEGORYNEED RANKING
123WaterSki1
357WaterSki1
741WaterSki1
713WaterSki1
157WaterSki1
627WaterSki1
987SnowSki2
159SnowSki2
963SnowSki2
379SnowSki2
564Biking3
753Biking3
618Biking3
852Hiking4
279Hiking4
159Walk5
1 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
)

 

View solution in original post

2 REPLIES 2
CNENFRNL
Super User III
Super User III

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
)

Screenshot 2021-06-30 220900.png

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
)

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors