Group by Dimension based on Max Sales

[#1]

I have a scenario where a customer can exist in multiple territories.  I want to show a list of customers where only the territory with the most sales are shown, but still have the total sales, independent of territory.

Example of data (other fields exist, such as month):

Customer	Terr	Priority	Sales
Organization 1	1B2	High	100
Organization 2	1B2	Medium	100
Organization 2	1B3	Medium	200
Organization 2	1B4	Medium	300
Organization 3	2B1	Low	100
Organization 3	2B3	Low	200
Organization 4	2B3	High	100
Organization 5	2B4	Medium	100
Organization 6	2B4	Low	100
Organization 7	2B4	High	100
Organization 7	3B1	High	200

This is how I would like to show the data, preferably without changing the load script:

Customer	Terr	Priority	Sales
Organization 1	1B2	High	100
Organization 2	1B4	Medium	600
Organization 3	2B3	Low	300
Organization 4	2B3	High	100
Organization 5	2B4	Medium	100
Organization 6	2B4	Low	100
Organization 7	2B4	High	300

How can I build the Territory dimension to accomplish this?

Thanks for any help that you can provide!

Sam

[Oleg_Troyansky]

List Territory as an Expression, using the function FirstSortedValue, with the amount of Sales as the sorting parameter.

[#3]

Thanks for the quick response.

I just tried this and although the formula validates, I cannot get it to return the correct results.  Here is my formula:

=FirstSortedValue([Terr], (-aggr(sum( { < [Month]=, [Month Rank] = {1,2,3},  [Brand]= {'BRANDA'} >}  [Sales]), [Customer])))

Any idea what I am doing wrong?  My understanding is that adding the minus sign before the aggr function will change it to the last sorted value (Largest sales number).  I can't see any specific logic to how the Terr is selected.

[#4]

Hi,

Try this:

Dimension: Customer

Expression 1 (Terr):  FirstSortedValue(Terr,-aggr(Sum(Sales),Customer,Terr))

Expression 2 (Priority) : FirstSortedValue(Priority,-aggr(Sum(Sales),Customer,Terr))

Expression 3 (Sales): Sum(Sales)

Regards!

[#5]

Very helpful Francisco!

Last thing.  I have one example where the sales are the same for an account in each territory.  It returns null and I am fine with it showing either territory.  How can I have it populate with a territory?

[#6 · Accepted solution]

Hi ,

I think that using distinct you can solve the problem:

FirstSortedValue(distinct Terr,-aggr(Sum(Sales),Customer,Terr))

The  function (FirstSortedValue) has a parameter for this case (n as order), but i've tried apply it unsuccessfully...

Regards

[#7]

Perfect.  Thanks!!!