How to use Rank and Aggregate while ignoring a selection?

[#1]

Dear all,

I want to find the company with the highest sales of the product 'Pencil'. I have a calculated dimension that works when all companies are selected. However, for some reason I need the dimension to work when only one company is selected and that does not work.

This is the dimension I use:

=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 2, Companies), Companies)

Does someone know the solution?

Many thanks!

Mark

[rubenmarin · Accepted solution]

Hi MArk, if you don't want the selections to filter you can use a dimension like:

=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 1, Only({1}Companies)), Companies)

(With "supress null values" checked)

and a expression like:

SUM({1< Year = {2014}, Product = {Pencil}>} Sales)

[#3]

Thank you Ruben, that worked perfectly for me!