2 Replies Latest reply on Aug 23, 2016 7:09 AM by Winson Lui

# Lookup the result of maximum amount of sum and the corresponding value

HI,

I would like to get the result of maximum of count and its corresponding value of a dataset.

The format of the list is like this.

RegionCountry
Accound IDCount
EuropeA111
EuropeA121
EuropeA131
EuropeA141
EuropeB211
EuropeB221
AsiaC311
AsiaC321

I would like to find out the region which has the larget amount of customers and the corresponding amount.

Since the count no. is always 1, I believe it would be correct if we count the occurence of the region in the dataset.

In the example, the outcome would be Europe and 6.

Would you please tell me which formula I should use to get the result Europe?

My final goal is to get a list of countries who are under the region with largest amount and their counta.

In this case, it would be A, 4 and B,2.

I hope the first solution can act like a filter which when I update the dataset, the results will change automatically.

Thanks.

• ###### 1. Re: Lookup the result of maximum amount of sum and the corresponding value

Hi Winson,

One possible approach is the following:

1. Set up your dashboard: drag Region and Country to Columns, and SUM(Count) to Text.

2. Create a calculated field, TotalByRegion, that is an LOD working on the Region level and ignoring the Country level:

{FIXED [Region]:SUM([Count])}

Drag the TotalByRegion to Text to see that it works properly: all countries in Europe will have total of 6, and all countries in Asia - total of 3.

3. Create another calculated field, RankByRegion, as follows:

RANK(ATTR([TotalByRegion]))

Using ATTR is safe, as the previous definition guarantees TotalByRegion is constant for every region.

4. Now, drag RankByRegion to filters, and choose 1 as the only allowed value. That guarantees we are choosing only the region with highest rank, i.e. highest total.

I'm attaching a workbook using the table you proposed above. Let me know if you have any questions.

1 of 1 people found this helpful
• ###### 2. Re: Lookup the result of maximum amount of sum and the corresponding value

It is useful.

Thanks