7 Replies Latest reply on Sep 16, 2019 10:08 AM by Gerardo Varela

# Create a top and group the rest in "others"

Hi guys,

I'm having some issues with a table I am creating. For info the table contains various parameters so you can switch what you see between the x  ; y  axis.

On the photo you can see what I have and what i need.

I would like to keep only the first five columns (A;B;C;D.E)

BUT I also need to keep a 6th column called "others" which would regroup the rest (in this case "Others" = F; G; H; I")

What I have:

What I need:

Do you know how I can get this result?

Unfortunately i cannot share the workbook

Max

• ###### 1. Re: Create a top and group the rest in "others"

IF [Dimension] = "F" OR [Dimension] = "G" OR [Dimension] = "H" OR [Dimension] = "I" THEN "Others"

ELSE [Dimension] END

• ###### 2. Re: Create a top and group the rest in "others"

Unfortunately, as I said I have multiple parameters which will change the x; y  axis as well as filters which will change the column names.

Therefore i need a generic formula which would ideally would look like    :  first 5 [Dimension]  else "Others"

I was able to create a parameter in order to get the first 5 dimensions but I am unable to group and keep the rest.

• ###### 3. Re: Create a top and group the rest in "others"

Then you'll have to make this type for every dimension you use where it's equal to the first 5 then ELSE "Other".

Then combine the altered dimensions to use with your parameter

• ###### 4. Re: Create a top and group the rest in "others"

Interestingly, you can also say

IF [Dimension] > "E" THEN "Others"

ELSE [Dimension] END

( or >= "F" ... )

Gotta be careful when doing this, but it looks like for this specific example, it would work.

(Edit to add:  While I was typing that, additional discussion ensued.  Makes my post moot.)

• ###### 5. Re: Create a top and group the rest in "others"

Maxime Huot wrote:

Therefore i need a generic formula which would ideally would look like : first 5 [Dimension] else "Others"

Will the first 5 values always be alphabetically the first 5?

• ###### 6. Re: Create a top and group the rest in "others"

Hey,

Unfortunately no.

A B C D E, represents the "job title" of the clients.
In the filters you can select the area where the clients come from.

So depending on that filter the order as well as the header names will vary.
Also the table will be on the current month, the top 5 may vary every month...

• ###### 7. Re: Create a top and group the rest in "others"

Hi All,

Here are a couple of great threads:

Top N, with everyone else as "Other"

top n with "other" sum for arbitrary dimension depth

See attached workbook from the second thread.  There is a slew of Table Calculations so take some time to pick it apart.  I only fixed up to two dimensions but it should give you a good starting point.  Also, depending on your dataset you might have sub-par performance.

Regards,

Gerardo