cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
ricsno_8900s
New Member

RANK/RANKX

I have been going in circles and thought I would have a go at posting my first help request here so apologies for any rookie errors 🙂

My troubles revolve around what is a report I am working on for a school where I am hoping to rank pupils based on marks/results they achieved and make comparisons of these rankings across different assessments to spot trends. Here is a set of dummy data that replicates the relevant bits of the dataset I have: PBIX for Sample data 

 

I have two tables: 'Pupils'

ricsno_8900s_0-1689753837523.png

 

and 'Results'.

ricsno_8900s_1-1689753855727.png

 

I'd like to rank the pupils based on their [Yr] which is the the 'Pupils' table and based on their 'Results' by 'Results name' (which are both in the Results table). My attempt at ranking these either give errors or seem to create extra rows for each pupil. 

 

My goal is to show Bob as ranked 1 for IQ for Yr 5 and I'd like to be able to use this ranking in a Card and in other visuals regardless of the filters on that page or :

ricsno_8900s_2-1689753954403.png

 

or Tina is ranked 1 for IQ for Yr 6:

ricsno_8900s_3-1689754000494.png

 

I'd need to keep going with things like where they rank for Maths and English in their Yr so show that Matt is ranked 1 for Maths for Yr 6 or Tom is ranked 1 for Yr 5 English.

 

For a bonus, I would also like to rank them for their average score combined between English and Maths (i.e. the scores averaged for each pupil and then ranked by year).  

3 REPLIES 3
baghdadi62
Resolver III
Resolver III

I have simply added students' information as a new column to the results table. It seemed like an easy method to me.

baghdadi62
Resolver III
Resolver III

Hi, you can create the "Name" and "Year" columns in the results table (using related() function if there is relationship between two tables), and then use the following DAX formula for calculating the rank:
dx1.JPG

I kind of get the understanding that it would be better to create a calculated table (using the related function) for each of the result names and then apply the ranking measures on that. Is that a viable method?

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors