Find Max rank from a ranking column and fixed it so it doesn't change when filter applied.

verytiredgirl
verytiredgirl Member
in Charting

Say I have dataset ranking all the sales rep by their Margin FYTD:

SALES REP

CURRENT MARGIN

RANK

A

0.5

1

B

0.4

2

C

0.3

3

D

0.2

4

E

0.1

5

I'd like to add a text column so instead of rank 1 it's '1 of 5', etc. To do so I need:

CONCAT(`RANK`, ' of ', MAX(`RANK`) FIXED())

which is easy but the hard part is if I'm interested in just 1 or 2 sales rep and their rankings comparing with all the rest, I filter Sales Rep 'A' & 'B',

SALES REP

CURRENT MARGIN

RANK

Desc

A

0.2

1

1 of 2

B

0.3

2

2 of 2

it would show '1 of 2' and '2 of 2' which is just comparing the 2 of them not the rest. So ideally, the Max(rank) should be fixed even when filter is applied so that it show '1 of 5, '2 of 5' even when i have select only 2 people.

Tagged:

Answers

Categories