Counting Ranks

[Darren3000]

Got a Rank and Count issue unsolved:

Lets say there are 5 people, Adam, Bob, Calvin, David and Eden participating in 5 activities, namely Soccer, Basketball, Softball, Badminton and Tennis. And the excel file records each of their score in five column, ranging from B2:F6, and each of the participants' names stated in A2:A6, and the names of each activities stated in B1:F1

Just wonder if theres any excel formula to calculate the times that Adam achieved rank 1-3 in the 5 activities without separately calculating the rankings for each activity.

Thanks for answering!

[DataNinja777 · Accepted solution]

Hi @Darren3000 ,

I see you’re looking for a way to calculate how many times Adam (or any participant) ranked in the top 3 across multiple activities. You mentioned an Excel solution, but since this is a Power BI forum, let’s make this more robust using DAX! Here's how to do it in Power BI without manually ranking each activity.

Participant	Activity	Score
Adam	Soccer	85
Adam	Basketball	90
Adam	Softball	78
Bob	Soccer	88
Bob	Basketball	85
…	…	…

Then, create this DAX measure to calculate how many times each participant ranked in the top 3 across all activities:

Top3RankCount =
CALCULATE(
    COUNTROWS(
        FILTER(
            VALUES(Scores[Activity]),
            RANKX(
                ALL(Scores[Participant]),
                CALCULATE(SUM(Scores[Score])),
                ,
                DESC
            ) <= 3
        )
    )
)

• RANKX: Ranks participants based on their scores for each activity.
• FILTER: Checks if the rank is within the top 3.
• CALCULATE: Ensures that each participant’s rank is calculated correctly in the given filter context.

Best regards,

[v-huijiey-msft · Accepted solution]

Hi @Darren3000 ,

Thanks for the reply from DataNinja777 .

Here is the sample data I created from your description:

Name	Soccer	Basketball	Softball	Badminton	Tennis
Adam	80	90	85	75	88
Bob	70	85	90	80	85
Calvin	90	70	75	85	95
David	85	80	70	90	75
Eden	75	75	80	95	70

Please create a measure that names the variables for calculating the rankings in the different categories so that you can count the number of times each person reached 1st-3rd place in each of the 5 activities. Using Adam as an example, he placed 1-3 in all events except Badminton, so the result is 4.

Adam_Rank = 
VAR SoccerRank =
    RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Soccer] ) ),, DESC, DENSE )
VAR BasketballRank =
    RANKX (
        ALL ( 'Table' ),
        CALCULATE ( SUM ( 'Table'[Basketball] ) ),
        ,
        DESC,
        DENSE
    )
VAR SoftballRank =
    RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Softball] ) ),, DESC, DENSE )
VAR BadmintonRank =
    RANKX (
        ALL ( 'Table' ),
        CALCULATE ( SUM ( 'Table'[Badminton] ) ),
        ,
        DESC,
        DENSE
    )
VAR TennisRank =
    RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Tennis] ) ),, DESC, DENSE )
RETURN
    COUNTROWS (
        FILTER (
            { SoccerRank, BasketballRank, SoftballRank, BadmintonRank, TennisRank },
            [Value] <= 3
        )
    )

The final page result is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[DataNinja777 · Accepted solution]

Hi @Darren3000 ,

I see you’re looking for a way to calculate how many times Adam (or any participant) ranked in the top 3 across multiple activities. You mentioned an Excel solution, but since this is a Power BI forum, let’s make this more robust using DAX! Here's how to do it in Power BI without manually ranking each activity.

Participant	Activity	Score
Adam	Soccer	85
Adam	Basketball	90
Adam	Softball	78
Bob	Soccer	88
Bob	Basketball	85
…	…	…

Then, create this DAX measure to calculate how many times each participant ranked in the top 3 across all activities:

Top3RankCount =
CALCULATE(
    COUNTROWS(
        FILTER(
            VALUES(Scores[Activity]),
            RANKX(
                ALL(Scores[Participant]),
                CALCULATE(SUM(Scores[Score])),
                ,
                DESC
            ) <= 3
        )
    )
)

• RANKX: Ranks participants based on their scores for each activity.
• FILTER: Checks if the rank is within the top 3.
• CALCULATE: Ensures that each participant’s rank is calculated correctly in the given filter context.

Best regards,