4 Replies Latest reply on Sep 11, 2018 12:03 PM by Melissa Fanucci

# Need to create a group/set for "Other" on a bar chart that changes by sheet

Hi all! I've done some digging around here and found some answers to this but nothing that fully solved my problem so I hope you can help me. I'd like to group all values under a certain percentage of the total (say, under 5%) into an "Other" line. But when I do it to one sheet it affects the other sheets in the workbook even though the values change.

1. How would I write a calculated field that separates all number of record values under a certain percentage threshold;

2. How do I apply that calculated field so it creates an "Other" bar in my bar chart?

In the sample database below, I have two sheets - one showing genres by Disney, one showing genres by all other studios. I'd like to group anything under 5% for both of these sheets. Help!

Melissa

• ###### 1. Re: Need to create a group/set for "Other" on a bar chart that changes by sheet

HI Melissa

It's little bit tricky to handle this type of dynamic table calculations.

But here is one idea.

Don't use "Filter " because it breaks percentage calc, se "HIDE" instead.

Changing filter does not impact to the structure of percentage calc.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Need to create a group/set for "Other" on a bar chart that changes by sheet

Hi Melissa, you could do this with a Fixed Formula.

I created a parameter called Other Threshhold %.

I then created a calculation to find the % of Records for your table which uses the FIXED formula to get total sum of records as the denominator.  The calculation simply says: IF % of Records < Other Threshhold % parameter, then return "Other" else return the "Genre" name.

Because we are using the FIXED formula, you will need to add the Lead Studio filter to Context in order for that filter to be taken into account by the calculation.

To do this, click the right arrow on the Lead Studio pill in the Filters pane, and click Add to Context.

I've attached the workbook. I hope this helps.

Best,

Jennifer

1 of 1 people found this helpful
• ###### 3. Re: Need to create a group/set for "Other" on a bar chart that changes by sheet

Thank you so very much for your detailed explanation. I was able to recreate. I truly appreciate it!

Melissa

• ###### 4. Re: Need to create a group/set for "Other" on a bar chart that changes by sheet

Thank you so much!