Problem with Rank and Totals

[#1]

I'm having an issue using the Rank function in a pivot chart table. I'm trying to rank all customers according to their sales for the selected year and the previous year. But I only want to display the top 5. I could live with a chart that shows only the first five lines -- and I get the desired rankings when I do this. But the totals reflect ALL customers, not just the Top 5.

When I change the Dimension Limit to show only the largest 5, I get the totals I want, but the previous year ranking is now relative to the top 5 only.

Do you see a way to get the ranking on the whole set, but the totals for the Top 5 only? See attached which has explanatory notes.

Thank you.

[Anonymous · Accepted solution]

Hi,

You may use =MAX(AGGR(RANK(SUM({<Customer=,Year={$(=Only(Year)-1)}>}Sales)),Customer))

in the second chart.

Best regards,

Cosmina

[#3]

That works well. Thank you!