DAX Measure to rank by category and subcategory

[john-paul]

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.

City	Product	Ref	RANK I want
Sydney	Bike	100	1
Sydney	Bike	102	2
Sydney	Bike	105	3
Sydney	Bike	112	4
Sydney	Bike	113	5
Sydney	Car	101	1
Sydney	Car	103	2
Sydney	Car	104	3
Melbourne	Bike	106	1
Melbourne	Bike	108	2
Melbourne	Bike	111	3
Melbourne	Car	107	1
Melbourne	Car	109	2
Melbourne	Car	110	3

<tbody>
</tbody>

Hope you can help.

Thanks,
John

 

[gazpage]

Given you haven’t had a response...

You want something like:

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

 

[john-paul]

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]

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

 

[john-paul]

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)
)