Ranking 04-25-2017 19:37 PM - last edited 04-25-2017 19:37 PM

[GilbertQ]

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}

)

eyJrIjoiYTRkN2VhYmEtY2U1OS00Nzg0LThiODgtZDZhYTdlYWY2MTQ4IiwidCI6ImQ3MGI0ZDU1LWE1OTItNDVmMi05OWYzLTI5OWFlNWVlN2FkZSJ9

[Quentin]

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]

Hi there that is correct, if you had to use the ALL it would keep the ranking no matter what you selected.

[alissa]

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.