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

[Oleg_Troyansky]

Hi Thom,

I think you should add a TOTAL keyword and play with the combination of the TOTAL dimensions. In a Pivot table, the ranking applies to the complete set of chart dimensions - in your case, Salesperson, Services, and Year. Perhaps you need the TOTAL by Salesperson? Or by Salesperson and Year?

Are you planning to come back to one of our Master Summit sessions? We are changing the content quite a bit this year!

cheers,

Oleg Troyansky

[tmumaw · Accepted solution]

Hi Oleg,

Yes I do want to try and make it back to one of the Master Summits, but trying to get away lately has been pretty difficult. Thanks for the response.  Did you mean try the total on the rank or the sum, or within the sort?

Thom

[Oleg_Troyansky]

I meant the total in the Rank()

[tmumaw]

It appears there is no way to rank a pivot table the way I want to (at least I have not figured it out).  I have a salesperson, services and I want to rank the dollar value for 2014 and 2015 by service by arborist.  what I would like to see is : Every time the arborist changes I need to rank total sales.  Thanks Oleg.....

[Oleg_Troyansky]

Hi Thom,

if you posted a small sample app, I could give it a try...

Oleg

[tmumaw]

I can email you a spreadsheet if that works?  Thanks for all your help.

Thom

[tmumaw]

Here is a small sample.  Hope it helps.  Thanks Thom

[Oleg_Troyansky]

Thom,

I can see that the TOTAL <dim> doesn't work in the Rank function - the helper popup text shows it, but it doesn't work. The general TOTAL works - you can calculate RANK across all Arborists and Services.

I'm not sure what's your desired result though. Can you attach a working example in Excel?

Oleg

[Oleg_Troyansky]

Attaching the document that I have so far ...