Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Cgowdar
Frequent Visitor

Displaying Top 3 and Others based on dynamic ranking

The Requirement is to show the "Top 3" and "Others" in the Pie Chart.

 

Following is the sample data:

 

SubjectStudent Marks
MathsJohn50
Maths Sam60
MathsVan70
MathsJim30
Maths Jam20
MathsCan90
EnglishJohn20
EnglishSam30
EnglishVan40
EnglishJim50
EnglishJam60
EnglishCan70
FrenchJohn55
FrenchSam75
FrenchVan15
FrenchJim95
FrenchJam80
FrenchCan20

 

Subject will be the filter.

 

need to obtain Top 3 Students based on their Marks. the ranking will change for every subject choosen from the filter.

 

I have tried using RANKX Function , but not able to get to the solution.

 

Any suggestions on this will be helpfull.

 

Thanks in advance

1 ACCEPTED SOLUTION

@Cgowdar

 

You can also use a MEASURE. This will allow you to determine RANK when you select more than 1 subject

 

RANK =
RANKX (
    ALLSELECTED ( TableName[Student ] ),
    CALCULATE ( SUM ( TableName[Marks] ) ),
    ,
    DESC,
    DENSE
)

 

 

1043.png


Regards
Zubair

Please try my custom visuals

View solution in original post

9 REPLIES 9
Zubair_Muhammad
Community Champion
Community Champion

 @Cgowdar

 

How about adding a Calculated Column that will RANK students for each subject separately

 

RANK in each Subject =
RANKX (
    FILTER (
        ALL ( TableName ),
        TableName[Subject] = EARLIER ( TableName[Subject] )
    ),
    TableName[Marks],
    ,
    DESC,
    DENSE
)

Regards
Zubair

Please try my custom visuals

@Cgowdar

 

1041.png


Regards
Zubair

Please try my custom visuals

HI @Cgowdar

 

See the attached file here

 

Now you can also add another Column that will help in Pie Chart

 

Is Top 3 Student? =
IF ( TableName[RANK in each Subject] <= 3, TableName[Student ], "Others" )

1042.png


Regards
Zubair

Please try my custom visuals

Thanks for the reply , this works perfectly fine.

 

but facing isssues while i have to apply other filers on the data 

 

for example , currencly the filter is only on "Subject"

 

we may also have additional filters like "Month" or "Grade / Standard " etc

 

Kinldy share your views on this issue if possible.

 

Thanks in advance

HI @Cgowdar

 

I believe this MEASURE should work fine even with other FILTERs

 

To test it I added another column in your sample data "YEAR" and added it as a slicer

 

See the attached file here

 


Regards
Zubair

Please try my custom visuals

This measure is fine , but our main moto was to display the "Others" pie slice on the chart , where as this measure helps in displaying in the tool tip.

 

Thanks

@Cgowdar

 

OK, So you want to Group the "OTHERS" together which a MEASURE doesnot allow..Right?


Regards
Zubair

Please try my custom visuals

Yes , Exactly

@Cgowdar

 

You can also use a MEASURE. This will allow you to determine RANK when you select more than 1 subject

 

RANK =
RANKX (
    ALLSELECTED ( TableName[Student ] ),
    CALCULATE ( SUM ( TableName[Marks] ) ),
    ,
    DESC,
    DENSE
)

 

 

1043.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.