# DAX Measure to rank by category and subcategory



## john-paul (Jan 19, 2019)

Hi,

I have loaded a table (named "Data") into Powerpivot and would like a measure to rank by Product in each city using a ref number. 
I also want this rank to stay even if I filter later.


CityProductRefRANK I wantSydneyBike1001SydneyBike1022SydneyBike1053SydneyBike1124SydneyBike1135SydneyCar1011SydneyCar1032SydneyCar1043MelbourneBike1061MelbourneBike1082MelbourneBike1113MelbourneCar1071MelbourneCar1092MelbourneCar1103

<tbody>

</tbody>
Hope you can help.

Thanks,
John


----------



## gazpage (Jan 26, 2019)

Given you haven’t had a response...

You want something like:

=RANKX(
    ALLEXCEPT( Product, Category ),
    SUM( Reference )
)


----------



## john-paul (Jan 28, 2019)

Thanks gazpage.

Based on your suggestion I have used: 

RANKX(
ALLEXCEPT( Data, Data[Product], Data[City]) ,
CALCULATE(SUM( [Ref]) )
)

This seems to work great.
Do you know if I can reverse the rank order so that lowest ref number is ranked first etc?

Thanks again,
John


----------



## gazpage (Jan 29, 2019)

Just google RANKX DAX. You will need another parameter, I forget if is is a 0 or 1.


----------



## john-paul (Feb 13, 2019)

I have it working with the below, but each rank commences with 2 - there is no 1st rank.

Anyone able to help? Thanks.

IF(
  HASONEVALUE( Data[Product] ),
  RANKX(
     ALLEXCEPT( Data,Data[Product],Data[City]),
     [Sum_REF] , , 
     ASC,Dense)
)


----------

