Show correct ranking in a pivot table

[ckmchinmaya]

Hi Friends,

I got stuck in a rank calculation. I am using a pivot table where 3 dimensions are there Product, Sub-Product and City.

One expression is calculating Achievement % and Rank of that against all 4 cities. The Rank column is working fine where all are unique values. It get stuck when two/more values are same as shown in the table below. 

For DIM 2, City 4 rank should be 2, but its showing 4.

I am using rank(expr, 1, 1) in the expression

Product    Sub_Product    City             Performance       Rank

A                   A1                         City1            100%                       1

A                   A1                         City2            100%                       1

A                   A1                         City3            100%                       1

A                   A1                         City4             98%                         4           ( it should be 2)

Please let me know, if any further information required.

Thanks in advance.

Regards,

Chinmaya

[Frank_Hartmann]

try this:

Div(

  Index(

  '-' & Concat(DISTINCT TOTAL num(aggr(rank(sum(Performance),1,1),City), '0000'), '-', aggr(rank(sum(Performance),City),City)),

  num(aggr(rank(sum(Performance),City),City),'0000' )

  )

  ,5)+1