4 Replies Latest reply on Apr 12, 2018 3:15 PM by Randolph Ralph

# Exclude Row Value from Subtotal

I am attempting to exclude a value from being included in a subtotal. My sheet is calculating sales, revenue, and revenue per sale. There are two dimensions under row (Type and Category). I am attempting to not include a revenue per sale calculation where the Category is Other, and also exclude this category from the row subtotal. I have attached my Tableau workbook with a calculated field named Test-Revenue per Sale in which I have attempted to exclude the Other Category, but the Subtotal will not calculate and shows blank.

Here is what I am attempting to do.

• ###### 1. Re: Exclude Row Value from Subtotal

Hi Randolph, can you re-attach your workbook, I cannot find any attachment.

Soham

• ###### 2. Re: Exclude Row Value from Subtotal

Sorry about that. Not sure what happened, but have attached the file.

• ###### 3. Re: Exclude Row Value from Subtotal

Randolf,

you can use the following calculation

if first() != last()

then

//cell values

if ATTR( [Category] ) = 'Other' then 0 else [2016 Revenue]/[2016 Sales] end

else

//Total values

[2016 Revenue (woOther)] / [2016 Sales (woOther)]

end

this calculation must be computing using  Pane Down.  This way  first() and last()  will both equals 0 for the Totals, and have a different value for each other cell. This way you can have a different calculation applied on the Total than the one applied on the cells.

Your attempt returns null, because for the Total  Tableau don't take the level of detail into account.  So for the Total ATTR( [Category] )  was returning null, because there is not a single unique Category in the view. With my solution, I get rid of the ATTR() function for the Total by computing  the Revenue and Sales without the Category 'Other'.

Michel

• ###### 4. Re: Exclude Row Value from Subtotal

Thank you that worked perfectly. I am hanging on to that solution.