GilbertQ's avatar
GilbertQ
Super User
9 years ago

Ranking

This is a simple measure but one that I have used countless times for ranking of products or in this example Surfers and their prize money.

 

This rank can also be used in conjunction with the TOPN or BOTTOMN filters 

 

NAME:

Rank per category

 

DESCRIPTION:

Calculate the rank of the base value within the category

 

PARAMETERS:

Name: Base value

Tooltip: The value you want to average

Type: Numerical field / measure

 

Name: Category

Tooltip: The category in which you want to calculate the rank

Type: Categorical field

 

DAX:

Rank {Base value} per {Category} =

RANKX(

  ALLSELECTED({Category}),

  {Base value}

)

 

 

Other

3 Replies

  • alissa's avatar
    alissa
    Frequent Visitor
    7 years ago

    This is awesome, and so streamlined! Could one use similar code to calculate a percentile (like PERCENTRANK in Excel)? What would that look like? I've been trying to implement something like this with DAX and it's becoming way too memory intensive.

  • Quentin's avatar
    Quentin
    Helper IV
    6 years ago

    Thank you for sharing! 🙂 

    I'm facing this issue that when someone select a surfer, it filters the table and take the filtered table in the DAX measure. THerefore selecting a surfer always show of ranking of 1 while it could be more interested to see the ranking in the overall table.

    I think something can be done using All instead of Allselected in the formule 

     

    Regards,

    • GilbertQ's avatar
      GilbertQ
      Super User
      6 years ago
      Hi there that is correct, if you had to use the ALL it would keep the ranking no matter what you selected.