RANK set analysis

[#1]

I have data with

SALESPERSON
SALESGROUP
PRODUCT
CLIENT
REVENUE

I'm trying to create a text box which when I select a SALESPERSON, I'm able to get the output:

"Rank is 2 of 16"

meaning Rank is 2 of 16 in this PRODUCT of all SALESPERSONs in that SALESGROUP.

I know rank doesn't take set analysis, but I was wondering if this is at all possible? The way I have it now, whenever I click a salesperson, they become "Rank is 1 of 1".

Thanks in advance!

[#2]

What expression are you currently using? I think you should be able to solve the problem by using the TOTAL modifier. Something like:

'Rank is ' & Rank(PRODUCT) & ' of ' & Rank(TOTAL PRODUCT)

You may need to modify the TOTAL modifier slightly depending on how that grand total should be calculated. For example if it is rank in that particular Sales Group, then:

'Rank is ' & Rank(PRODUCT) & ' of ' & Rank(TOTAL <SALESGROUP> PRODUCT)

[#3]

I was trying to use an expression along the lines of:

'Rank is ' & rank(Sum(REVENUE) & ' of ' & count(Total SALESPERSON) but this wasn't working as whenever I would select a SALESPERSON,

the output would always be 'Rank is 1 of 1'.

I tried using your expression but am getting a null value.

I'm wondering if I should rank within the script to hardcode a rank value as an alternative option.

[#4 · Accepted solution]

Oh, I missed the part about the selection. You want to ignore the selection on SALESPERSON. Probably:

'Rank is ' & rank(Sum(REVENUE)) & ' of ' & count({<SALESPERSON=>} SALESPERSON)

That second part should get you the rank of the SALESPERSON ignoring any selections on that field. You may need the TOTAL too depending on how your dimensions are set up in the chart.

'Rank is ' & rank(Sum(REVENUE)) & ' of ' & count({<SALESPERSON=>} TOTAL SALESPERSON)

You may need to modify that Set Analysis if you are making selections on other fields that you don't want to affect the count. If you are selecting one SALESPERSON directly, that should work.

[#5]

Thank you! adding in the ignore selection piece fixed it...