Ranking within a pivot table

[tmumaw]

Hello All,

I have a small problem and I know that I am not the only one that has been presented with this.  I have a pivot table which is made up of sales people and services sold by year.  I am trying to rank on 2015 and 2014 all services by sales person.  I have tried everything.  When I select only a sales person it ranks fine, but when I have multiple sales people the ranking is all over the place. My dimensions are Salesperson, services, Year summing sales amount.     Here are the expressions:

Rank: rank(sum(Inv Sales))

Sales: sum(Inv Sales)

Sort :  Services : aggr(sum({<FISYR = {$(=$(vFisYr) - 1)}>} [Inv Gross]),[Material - Code & Name])   decending....

Thanks

[tmumaw]

Maybe we look at this a different way.  Is there a way to tell me who the number one arborist is for a service and ignore the rest?

Thanks

Thom

[Oleg_Troyansky]

It sounds like you want to ignore Years... Something like this (attached)?

[tmumaw]

This might work.....I changed it to a straight table, removed the year and used the dimension limits......it shows me the top arborist for a service.  What do you think?

Thanks

Thom

[Oleg_Troyansky]

Well, I didn't know all these changes were on the table 🙂 Sure, it works!

[tmumaw]

Thank you very much for all your help.  Keep me posted on the Masters Summit.....Will you be at the QConference in May?

Thom

[Oleg_Troyansky]

Yes, absolutely!

[tmumaw]

Great.  I will look for you I owe you for all your help,..

Thom