3 Replies Latest reply on Jun 22, 2017 2:15 PM by Ron Bayes

# Exclude Group Dimension In Table Down Sum

I have a list of departments that are in a group.  Some departments  I do not want shown individually on my worksheet and are grouped together under the name 'Exclude' while the rest are appropriately listed.  I'm trying to calculate the % of total of all the departments listed on the worksheet except the 'Exclude' group.  The 'Exclude' group has to remain on the worksheet as I have other data points that are relevant.  In Excel terms, I have a table A1:J20.  I want to take the sum of E1 and divide that by the sum of E1:E19 to get the percent of total (E20 would be the 'Exclude' group that I do not want in the calc).

Any thoughts as to how I would set this up?

• ###### 1. Re: Exclude Group Dimension In Table Down Sum

Hi Ron

I would feel better about responding if I could see your data -

I tried to simulate what you want with superstore data and using an LOD expression to sum the total sales but to leave the ART sales out of the total yet have them available

{ INCLUDE  [Sub-Category]: sum({ FIXED ([Sub-Category])<>"art" : Sum([Sales]) })}

I think the calculation gets at what you want - you would need to get the display to work out for you

Let me know if this helps

Jim

• ###### 2. Re: Exclude Group Dimension In Table Down Sum

Here is a screenshot of what I'm working on.  So basically I would want a calc that takes, for example, SuperStar (Offered) / by the sum of all in the offered column with the exception of Exclude at the bottom of the page to give me the percent of total.

• ###### 3. Re: Exclude Group Dimension In Table Down Sum

Note that each row in the calc would be different.  So the following row would take Stars PSP (offered) / by the sum of all in the offered column with the exception of Exclude at the bottom of the page to give me the percent of total and so on.....