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...

But that doesn't work. How do I rank a column but only if it is one or the other?

Thanks,

• Edited by Friday, September 20, 2013 11:36 AM
Friday, September 20, 2013 11:17 AM

• 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 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 PM

The exact formula I used was...

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 PM
• 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 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 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