exclude selection and find top ranked sales

[arjunmadhusudan]

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

[sunny_talwar]

Try this

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

[arjunmadhusudan]

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.

[sunny_talwar]

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

[arjunmadhusudan]

Sunny,

Unfortunately this expression is not working.

[sunny_talwar]

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?

[arjunmadhusudan]

Sunny,

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

[sunny_talwar · Accepted solution]

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?

[arjunmadhusudan]

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).

Please help.

[sunny_talwar]

This should give you the company

=FirstSortedValue({<Company = e(Company)>} Company, -Aggr(Sum({<Company = e(Company)>}Sales), Company))