12 Replies Latest reply on Dec 13, 2018 12:18 PM by Norbert Maijoor

# How to exclude a sub category value in (Grand)Totals while displaying in the view?

How to exclude a sub category value in (Grand)Totals while displaying in the view?

I want to show Nevada's profit in the view but want to exclude in Total

 Country State Profit USA New York \$1,000 New Jersy \$1,000 Texas \$3,000 California \$1,000 Nevada \$5,000 USA Total \$6,000 but not \$11000 UK London \$1,000 Manchester \$2,000 Total \$3,000

Thanks,

Rao

• ###### 1. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Hi Rao,

Please check my approach as reference below and stored in attached workbook version 2018.2 located in the original thread

1. calculation:

if size()<>2 then sum([Profit])

elseif size()=2 then sum({fixed[Country]:sum( if [State]!='Nevada' then [Profit] end)}) end

2. Make sure Subtotals is available

PS just just size() in the viz to make solution clear but can be removed

Hope it helps,

Regards,

Norbert

• ###### 2. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Please find attached workbook for your reference. I have excluded Tables from Furniture Subtotal

1 of 1 people found this helpful
• ###### 3. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Thanks Norbert, but I could not open the attached workbook. I am using 10.5 version

• ###### 4. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Thanks Amit, it helped me.

it is working good if i do not have any aggregated measures.

But in my case the measures are calculated fields(percentage values), is there any anything else I can do to get the expected out put. I am getting error "can not mix aggregate and non-aggregate or results in 'if' expression"

Thanks,

Rao

• ###### 5. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Hi Rao,

Attached the workbook once again in version 10.5

Hope it helps,

Regards,

Norbert

1 of 1 people found this helpful
• ###### 6. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Hi Norbert,

Thanks for helping me in resolving my issue.

I was not too specific in my question earlier, is it possible to show the same view as below.

 USA New York \$1,000 New Jersey \$1,000 Texas \$3,000 California \$1,000 Nevada \$5,000 US Total \$6,000 UK London \$1,000 Manchester \$2,000 UK Total \$3,000

Thanks in advance.

Thanks,

Rao

• ###### 7. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

could you share sample workbook(twbx) with dummy data mimicking your scenario?

It is difficult to say anything otherwise.

• ###### 8. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Hi Rao,

Find attached my updated workbook version 10.5 located in the original thread.

1. Define a Union

2. Define D1. Display:

if [Table Name]='Sheet1' then [Country]

elseif [Table Name]='Sheet1\$' then [State] END

3. Define M1. Measure:

if size()=9 and attr([Table Name])='Sheet1\$' then sum([Profit])

elseif size()<>9 then sum({fixed[Country]:sum( if [D1. Display]!='Nevada' and [D1. Display]!='UK' and [D1. Display]!='USA'  then [Profit] end)}) end

Hope it helps,

Regards,

Norbert

1 of 1 people found this helpful
• ###### 9. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Thanks a lot Norbert.

• ###### 10. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

H Rao,

You are welcome. Thanks for the "Badge". Much appreciated

Regards,

Norbert

• ###### 11. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Hi Norbert,

Is this can be done if you have the data coming from database.

Thanks for your time,

Thanks,

Punneth

• ###### 12. Re: How to exclude a sub category value in (Grand)Totals while displaying in the view?

Hi Rao,

Sure it's based on an UNION in on Tableau

Regards,

Norbert