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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dphillips
Helper IV
Helper IV

Rank based on a measure

I have marks from students who have completed some tests. I have created a measure which gives me the average of the students results.

Scld Mark Ave =  AVERAGE(uncRedshift_Studentresults[Scld])

I have slicers on the page which filter the Fileyear and Student_ID so I get an average Scaled mark for a particular student in a particular year. This works fine.

 

Now I would love to give a rank to each student eg Student_ID 1234 with an average scaled mark of 62.45 is ranked 78th out of the whole year group of 230 Year 9 students in 2018. In other words, I want to rank the average scaled mark for this student against the average scaled mark for everyone else in the Year Group for a particular year. I want to put this in a card eg "78 out of 230". Not sure at all how to go about doing this?

 

I have provided some data with students with scaled marks in their subjects by Year and YearLevel.

 

https://docs.google.com/spreadsheets/d/1pYOpN_q_5KTVxu6ZqRqFIrOUiJLxS38XDubrNVWwdSo/edit?usp=sharing

 

Any help would be much appreciated.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@dphillips add following measure for rank 

 

Rank = 
RANKX( 
    FILTER( 
        ALL( Sheet1[FileYear], Sheet1[StudentYearLevel], Sheet1[StudentID] ),
        Sheet1[FileYear] = MAX(Sheet1[FileYear]) && 
        Sheet1[StudentYearLevel] = MAX( Sheet1[StudentYearLevel] )  
    ), 
    [Average Marks], , ASC, Dense
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@dphillips add following measure for rank 

 

Rank = 
RANKX( 
    FILTER( 
        ALL( Sheet1[FileYear], Sheet1[StudentYearLevel], Sheet1[StudentID] ),
        Sheet1[FileYear] = MAX(Sheet1[FileYear]) && 
        Sheet1[StudentYearLevel] = MAX( Sheet1[StudentYearLevel] )  
    ), 
    [Average Marks], , ASC, Dense
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  - Thanks for the info. I keep getting a rank of 1 for any student I select. Have looked at your measure and tested filters, and the aggregations used, substituted in actual values etc, but I still always get a rank of 1 no matter which student I select. Any thoughts about what I am doing wrong? This is my measure for getting the average scaled mark for a student.

Average Marks = AVERAGE([ScldMark])

I then have slicers on the page for Fileyear, StudentYearLevel and StudentID.

 

I want this to be displayed in a card on its own.

 

Perhaps the problem is that this needs to be in a table or matrix so it can rank against the other values in the visual. If that is the case, how can I get this to be a standalone rank in a card? (Just tested this theory in a table and found that your measure works great - everything correct  - thanks so much - but as I said - I want to try and create a kpi matrix which uses a card with just the rank for that one student)

Any thoughts?

@dphillips it should work, let me double check with slicer you mentioned in your message.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k - it does work great when in a table or matrix but my issue is I was trying to get this in a standalone card and create a sort of KPI for a student, rather than having a table with all  the students in it. Is this possible?

Thanks again for all your help.

@dphillips are you ok to share pbix instead me recreate everything at my end. Remove any sensitive it contains.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hey @parry2k . Thanks so much for your help. I duplicated the page I was using, got rid of lots of slicers and filters to try and work out what was goin on and then started afresh on a new page and the card works properly on that page. Went back to the original page and, in the end, just got rid of the card and put a new card in, used the measure and all was fine. Again - thanks for all your help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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