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.

Sydney

Sydney

Sydney

Sydney

Sydney

Sydney

Sydney

Sydney

Melbourne

Melbourne

Melbourne

Melbourne

Melbourne

Melbourne

<tbody>
[TD="class: xl63"]City[/TD]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Ref[/TD]
[TD="class: xl65, width: 64"]RANK I want[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl64"]1[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]102[/TD]
[TD="class: xl64"]2[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]105[/TD]
[TD="class: xl64"]3[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]112[/TD]
[TD="class: xl64"]4[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]113[/TD]
[TD="class: xl64"]5[/TD]

[TD="class: xl64"]Car[/TD]
[TD="class: xl64"]101[/TD]
[TD="class: xl64"]1[/TD]

[TD="class: xl64"]Car[/TD]
[TD="class: xl64"]103[/TD]
[TD="class: xl64"]2[/TD]

[TD="class: xl64"]Car[/TD]
[TD="class: xl64"]104[/TD]
[TD="class: xl64"]3[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]106[/TD]
[TD="class: xl64"]1[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]108[/TD]
[TD="class: xl64"]2[/TD]

[TD="class: xl64"]Bike[/TD]
[TD="class: xl64"]111[/TD]
[TD="class: xl64"]3[/TD]

[TD="class: xl64"]Car[/TD]
[TD="class: xl64"]107[/TD]
[TD="class: xl64"]1[/TD]

[TD="class: xl64"]Car[/TD]
[TD="class: xl64"]109[/TD]
[TD="class: xl64"]2[/TD]

[TD="class: xl64"]Car[/TD]
[TD="class: xl64"]110[/TD]
[TD="class: xl64"]3[/TD]

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