Answered by:
rankx formula not working
Question

So I have a column "Test or Quiz". Then another column "Grade". How would I rank them for Test and rank for Quiz by best score. This is in PowerPivot by the way.
I tried something like this...
=RANKX([TestOrQuiz],[Grade],,,Dense)
But that doesn't work. How do I rank a column but only if it is one or the other?
Thanks,
 Edited by DCDeez Friday, September 20, 2013 11:36 AM
Friday, September 20, 2013 11:17 AM
Answers

Hi DCDeez You maybe to use "Calcuated Column" in here. So, this made the error. You should use above formula in measure. if you want to use "Calculated Column". You can try as below formulas : COUNTROWS( FILTER('Student Grades', 'Student Grades'[TestOrQuiz]=EARLIER('Student Grades'[TestOrQuiz]) && 'Student Grades'[Grade] >= EARLIER('Student Grades'[Grade]) ) ) Regards,
 Marked as answer by DCDeez Tuesday, September 24, 2013 1:25 PM
Saturday, September 21, 2013 4:15 AM
All replies

Hi DCDeez
If you have a column to identify if the record is a test or a quiz, as in:
ItemID TestOrQuiz Grade 1 Test 10 2 Quiz 40 3 Quiz 50 4 Test 35 5 Test 25 You can use a DAX measure expression as:
[Rank by Grade]:=RANKX( ALLEXCEPT(Table1, Table1[TestOrQuiz] ) , CALCULATE( SUM( Table1[Grade] ) ) )
With the following result:
Row Labels Rank by Grade Quiz 3 1 2 2 Test 4 1 5 2 1 3 Would this help?
Javier Guillen
http://javierguillen.wordpress.com/Friday, September 20, 2013 1:26 PMAnswerer 
I used your formula and got "A circular dependency was detected:'Student Grades'[Rank By Grade],'Student Grades'[Rank By Grade],Student Grades'[Rank By Grade]."
The exact formula I used was...
[Rank by Grade]:=RANKX( ALLEXCEPT('Student Grades', 'Student Grades'[TestOrQuiz] ) , CALCULATE( SUM( 'Student Grades'[Grade] ) ) )
What am I doing wrong?
Friday, September 20, 2013 1:50 PM 
Do you have other calculated column definitions on your dataset? Can you provide a small sample of the table and columns?
Javier Guillen
http://javierguillen.wordpress.com/Friday, September 20, 2013 2:17 PMAnswerer 
Hi DCDeez You maybe to use "Calcuated Column" in here. So, this made the error. You should use above formula in measure. if you want to use "Calculated Column". You can try as below formulas : COUNTROWS( FILTER('Student Grades', 'Student Grades'[TestOrQuiz]=EARLIER('Student Grades'[TestOrQuiz]) && 'Student Grades'[Grade] >= EARLIER('Student Grades'[Grade]) ) ) Regards,
 Marked as answer by DCDeez Tuesday, September 24, 2013 1:25 PM
Saturday, September 21, 2013 4:15 AM 
Javier,
I have a few more calculated columns.
The table is huge so I'm not going to include all of it. But here is an example of another calculated column and the corresponding columns.
The calculated column DisplayName has a formula of "=IF(SEARCH("",[Version],,0),"Modular","New")
Version DisplayName
AB Modular
V10 Modular
 New
3.21 Modular
C.42 Modular
 New
 Edited by DCDeez Tuesday, September 24, 2013 1:15 PM
Tuesday, September 24, 2013 1:14 PM 
CDZung,
Your formula worked perfectly.
Thank you so much!
Tuesday, September 24, 2013 1:25 PM