Displaying Top 3 and Others based on dynamic ranking

[Cgowdar]

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

Following is the sample data:

Subject	Student	Marks
Maths	John	50
Maths	Sam	60
Maths	Van	70
Maths	Jim	30
Maths	Jam	20
Maths	Can	90
English	John	20
English	Sam	30
English	Van	40
English	Jim	50
English	Jam	60
English	Can	70
French	John	55
French	Sam	75
French	Van	15
French	Jim	95
French	Jam	80
French	Can	20

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

[Zubair_Muhammad]

 @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
)

[Zubair_Muhammad]

@Cgowdar

[Zubair_Muhammad]

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" )

[Cgowdar]

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

[Zubair_Muhammad]

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

[Cgowdar]

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

[Zubair_Muhammad]

@Cgowdar

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

[Cgowdar]

Yes , Exactly

[Zubair_Muhammad · 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
)