Rank - considering all the possible dimensions, selecting one by one dimension

[NadMar]

Hi to everybody,
We have 150 sales reps, and 15 Area manager, (each AM has 10 reps on average).
I need to show the global ranking even selecting a single Area manager:

this is not working:

aggr(
rank( sum( {<AM = >} Sale)))
,AM,SalesRep)

This is showing all the sales Rep even if I select one Aareamanager:
rank( sum( {<AM = >} Sale))

So if this is my fact table:

AM	Reps	Sales	Global Rank
Luigi	Lucia	16	1
Luigi	Alberto	15	2
Luigi	Andrea	12	4
Luigi	Mario	7	8
Mario	Luca	13	3
Mario	Antonio	11	5
Mario	Giorgio	10	6
Mario	Roberta	9	7

When select Mario i'll need to have this output:

AM	Reps	Sales	Global Rank
Mario	Luca	13	3
Mario	Antonio	11	5
Mario	Giorgio	10	6
Mario	Roberta	9	7

Thanks in advance!

Best regards,

[Zapparoli]

Hi NadMar,

I have loaded your data, and just Selecting Mario without any expressions, gives the expected Output:

If this is not what you want, can you further elaborate your question ?  

[NadMar]

Hi Matheus, 

tnks for the replay!

actually "Global rank" should be a formula, in example i have written is already calculated,

consider that sales will have 10K rows per each rep per month

so i have to sum the sales and ranking it

[Zapparoli · Accepted solution]

Nadmar, Thanks for the explanation.

In this case we do not use AGGR by Area Manager, just by your salesperson.

Try this:

=aggr(
	 rank(
     	sum({<AM = >} Sales))
,Reps)

 The result i got when selecting Mario was:

without selections:

Let me know if it works. 

[NadMar]

Sorry, i got stuck in a trivial..

Thank you very much!!

[NadMar]

can i ask the last advise, so the problem was that i had 2 dimensions, sales rep, and product category, and i was aggregating for the wrong dimension, 

now i would like to se the total rank for the total product, i tried to see if i can use dimensionality or rowno to put different formulass (one for the total and the other for the cell values:

So if i agregate it correctly the total is not going to appare, if i take out the product category the formula'll not work inside the cell

i ow you a beer

[Zapparoli]

Hi,

I couldn't understand your question. Can you elaborate it further or give some sample data with the expected output ?