Select the Company with Max sales

[crystles]

I am trying to create a formula for a SVG map. I have a list of three companies I am comparing. I need to have the map color the different states (USA) depending on which company has the most sales in that state.

Ex

Company A =Blue

Company B = Green

Company C = Orange

So if Company A has the highest sales in Georgia, then the entire state of Georgia will be Blue.

I have the basis for the equation, I just need the piece that defines the max for each state.

Example of formula

If( *code to find Max Company by sales* = CompanyA, RBG(Blue)

     If( *code to find Max Company by sales* = CompanyB, RBG(Green)

          If( *code to find Max Company by sales* = CompanyC, RBG(Orange) ))

Any ideas or suggestions would be greatly appreciated! Thanks!

[sunny_talwar]

May be this:

FirstSortedValue(Company, - Aggr(Sum(Sales), Company))

[sunny_talwar · Accepted solution]

Or this:

FirstSortedValue(Company, - Aggr(Sum(Sales), Company, State))

[Anil_Babu_Samineni]

Perhaps This

Should write this BG color under +

If( CompanyA = {'$(=Max(Sum(Sales)))'}, Blue()

     If( CompanyB = {'$(=Max(Sum(Sales)))'}, Green()

          If( CompanyC = {'$(=Max(Sum(Sales)))'}, Orange() ))

OR

If( FirstSortedValue(CompanyA,Sum(Sales)), Blue()

     If( FirstSortedValue(CompanyB,Sum(Sales)), Green()

          If( FirstSortedValue(CompanyB,Sum(Sales)), Orange() ))

Hope This Helps ...!

[crystles]

Thank you Sunny T for the quick response. I have been trying to incorporate this into my SVG map chart.

I have this as the Measure = Max(aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE))

and the Color Expression is

=if(FirstSortedValue(MFR_CODE, - aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE)) = 'BB', RGB(26,71,135),
     if(FirstSortedValue(MFR_CODE, - aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE)) = 'TH', RGB(135,37,26),
         if(FirstSortedValue(MFR_CODE, - aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE)) = 'IC', RGB(237,231,45))))

I thought it was working, but now the entire map is black? I don't have the disabled or the Base color as black so this has to be something the color expression is doing? I'm wondering if it is trying to color each state as each of the 3 three colors at the same time and it's turning them black? I've never had this error before?

[sunny_talwar]

I have never used the SVG maps before so not really sure what it might be doing. May be someone with experience is able to provide better directions

[crystles]

I'm not sure what I was doing before but the formula works perfectly now.

Thank you!