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