3 Replies Latest reply on Jul 19, 2016 12:55 PM by swaroop.gantela

# How to filter some categories on a worksheet, but not others

I have 2 filters for my worksheet (Business Unit and Cost Center). Is there a way to filter all categories (IT Hardware, Office Supplies, Non-Travel F&B, and Mobility) except Corporate Office Printing on my worksheet? I want the user to update the dashboard categories by selecting a Business Unit and Cost Center, but I don’t Corporate Office Printing to be filtered by Business Unit and Cost Center. This is because the user will filter on the Corporate Office Printing dashboard by using store and location instead of BU and CC.

Thank you,

Lindsay

• ###### 1. Re: How to filter some categories on a worksheet, but not others

If I understood correctly, you are wanting that no matter what filters are selected,

the Corporate Office Printing Category should show it's total amount.

If that is the case, you can try using a Level of Detail calculation to fix the amount,

but only for the Category in question:

IF ATTR([Category])<>"Corporate Office Printing"

THEN SUM([Value])

ELSE MIN({Fixed :SUM(IF [Category]="Corporate Office Printing" THEN [Value] END)})

END

which is saying, for the other categories, just return the Sum of [Value], which will be subject

to what ever filters are selected.

But for Corp, find all the lines that are of that category, sum the returned amount

and fix that quantity. That fixed quantity will be the same for all lines with that category, so I

took the min to just get the value.

• ###### 2. Re: How to filter some categories on a worksheet, but not others

Thank you for your response Swaroop. The only issue I am running into is an error message when trying to use the formula you provided. Any ideas to work around this?

Thank you,

Lindsay

• ###### 3. Re: How to filter some categories on a worksheet, but not others

Hmm. That does make things trickier.

Life before Tableau 9 Level of Detail Calculations | VizPainter

I made an attempt, but it has its drawbacks, and I'm not entirely sure

if it will work for your real setup. There are very likely other solutions

that may be more straightforward.

In general, I wanted to join the datasource to itself to get all combinations

of Categories.

First, I created a new column in my datasource called Key which is just all 1s.

I used that as the key for the self-join.

On Sheet3 of the attached, you can see every category split up by Business Unit

and by Category with their respective values, and then in the last column you

can see the value of the Duplicate copy of the Value, which is showing the total value.

On Sheet5, I used a calculation to get the Value, basically to say

if it's the Corp Category then give the total (duplicate) value, if not

just the regular value which will be subject to the filters on the screen:

IF FIRST()=0 THEN

IF ATTR([Category])="Corporate Office Printing"

THEN SUM([Value (Sheet1\$1)])/COUNTD([Cost Center])

ELSE SUM([Value])/COUNT([Value])

END

END

-Because of the self-join, the sum's are inflated and need to be divided by a count.

You will have to play with the denominator for the Corp Value. I had to divide

it here by CountD(Cost Center) to get it to work, but it may be different for your dataset.

-Also, because of the items I needed to place on the Details shelf, there were multiple

copies of values, and so I selected just the first using the First()=0 part.

This Table Calculation may not be the best for your setup.

-The use of this FIRST() method also affected the formatting of the labels such that

I couldn't get to show at the end of the bar. I tried using a Dual Axis with text (Sheet4)

but it wasn't that much better.