9 Replies Latest reply: Mar 21, 2018 6:48 AM by Sunny Talwar

# exclude selection and find top ranked sales

Hi,

I have data as below

Company    Sales

A                  1000

A                     400

B                    500

B                      20

C                  2000

D                    400

D                      20

I need to calculate     Sum of sales selected company  divided by   Sales of company with highest sale from the remaining list ( not selected)

Regards,

Arjun

• ###### Re: exclude selection and find top ranked sales

Try this

=Sum(Sales)/Max({<Company = e(Company)>}Aggr(Sum({<Company = e(Company)>}Sales), Company))

• ###### Re: exclude selection and find top ranked sales

This does not give me any output without selection. It is only after selection that I get some output. Is that correct ?

Also I need to verify the output on selection using this expression.

Thanks.

• ###### Re: exclude selection and find top ranked sales

Yes, what output do you expect to see without selection?

• ###### Re: exclude selection and find top ranked sales

Sunny,

Unfortunately this expression is not working.

• ###### Re: exclude selection and find top ranked sales

I am afraid, you will have to provide more information... what do you mean it is not working? Showing nulls? Showing 0s? Computer Shutting Down?

• ###### Re: exclude selection and find top ranked sales

Sunny,

I am getting sum of all companies rather than top sales company excluding selection. Please check.

• ###### Re: exclude selection and find top ranked sales

When I select D, I get this

Where D is 420 and C is the top one which is 2000... and 420/2000 = 0.21... do you agree?

• ###### Re: exclude selection and find top ranked sales

Sunny,

I checked again, it seems correct.

I also need to display of this company which is being used in denominator ( output of max expression).