Ranking among three groups > return name of top 2

[AnniekH96]

Hi everyone, 

I got a large dataset(2M> rows) with 5 columns:

• Institution
• Year
• Region
• Course
• Number of students

I want to rank the within each year, region and course the number of students. For example the first 4 rows have the same year, region and course so they get ranked together. 

Secondly, I want a column(or measure) that returns the institution of the highest two ranking institution, all others get the label "Others."

I can't seem to find a rankx that allows the multiple groups. I would really appreciate some help!!

Institution	Year	Region	Course	Number op students	Ranking	Grouping
30RR	2019	Friesland	totaal;008;bbl;4	15	2	30RR
30RM	2019	Friesland	totaal;008;bbl;4	12	3	Others
25PJ	2019	Friesland	totaal;008;bbl;4	47	1	25PJ
08PG	2019	Friesland	totaal;008;bbl;4	3	4	Others
25PJ	2019	Friesland	totaal;057;bbl;4	16	1	25PJ
08PG	2018	Friesland	totaal;008;bbl;4	38	1	08PG
25PJ	2018	Friesland	totaal;008;bbl;4	34	2	25PJ
30RM	2018	Friesland	totaal;008;bbl;4	30	3	Others
AATR	2018	Friesland	totaal;008;bbl;4	12	4	Others
30RM	2019	Midden-Utrecht	totaal;057;bbl;4	12	1	30RM
30RR	2019	Midden-Utrecht	totaal;008;bbl;4	17	2	30RR
30RM	2019	Midden-Utrecht	totaal;008;bbl;4	39	1	30RM
25PJ	2019	Midden-Utrecht	totaal;008;bbl;4	2	5	Others
08PG	2019	Midden-Utrecht	totaal;008;bbl;4	3	4	Others
AATR	2019	Midden-Utrecht	totaal;008;bbl;4	15	3	Others
30RM	2018	Midden-Utrecht	totaal;057;bbl;4	44	1	30RM
30RR	2018	Midden-Utrecht	totaal;057;bbl;4	7	2	30RR
30RR	2018	Midden-Utrecht	totaal;008;bbl;4	25	2	30RR
25LG	2018	Midden-Utrecht	totaal;008;bbl;4	38	1	25LG

[amitchandak]

@AnniekH96 , If [Number op students] is a column and you need column rank .

If nedd measure rank. Make sure [Number op students] is measure or create a measure and use that in rank = sum(Table[Number op students])

Rankx(filter(Table, [year] =earlier([year]) && [region] =earlier([region]) && [course] =earlier([course]) ),[Number op students])

Rankx(filter(allselected(Table[Year], Table[region], Table[course]), Table[, [year] =earlier([year]) && [region] =earlier([region]) && [course] =earlier([course]) ),[Number op students])

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...