Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

RANX reversed

Dear All

 

I am currently using a RANKX feature to be able to rank different value and I would like to adapt it with two options :

  • Reversed the rankx (the lower value has to have rank 1 )
  • With the value is 0 the ranking has to be the last one 

Formula I have : 

RankFOBusiness = RANKX(ALLSELECTED(Table1[Description]),CALCULATE(AVERAGE(Table2[Value])),,,DENSE)

 

What I have now :

SubjectsValueRankX
Paul91
Patrick82
Jean82
Camille63
Thomas 4

 

What I would like to have 

SubjectsValueRankX
Paul93
Patrick82
Jean82
Camille61
Thomas 4

 

Thank you all for your help on this topic

 

Regards

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your reply, and I think it should work.

    After you apply it to your model once changing it to average function, but if it does not work, please let me have your sample pbix file. And then I can come up with a more accurate solution.

    Thank you.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I hope you can get an idea.

     

     

    Rank measure: =
VAR newtable =
    FILTER ( ALLSELECTED ( Subjects ), CALCULATE ( SUM ( Data[Value] ) ) <> 0 )
VAR rankzerovalue =
    COUNTROWS (
        SUMMARIZE (
            ADDCOLUMNS (
                ALLSELECTED ( Subjects ),
                "@value", CALCULATE ( SUM ( Data[Value] ) )
            ),
            [@value]
        )
    )
RETURN
    IF (
        HASONEVALUE ( Subjects[Subjects] ),
        IF (
            SUM ( Data[Value] ) = 0,
            rankzerovalue,
            RANKX ( newtable, CALCULATE ( SUM ( Data[Value] ) ),, ASC, DENSE )
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks a lot , it seems okay !

      However I don't want the rank to be based on the sum of the value but the average of the value. And when I replace "sum" by "average" it doesn't seem to work ?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your reply, and I think it should work.

        After you apply it to your model once changing it to average function, but if it does not work, please let me have your sample pbix file. And then I can come up with a more accurate solution.

        Thank you.