13 Replies Latest reply on Sep 11, 2018 9:40 AM by Hari Ankem

# Filter shouldn't apply to more than one field on the same work sheet

Hello,

On my worksheet, there are 2 fields on Rows shelf and I am performing a dual axis on the fields to create a line chart that gives me one line of total(F1) count and another one for 2 categories. But when I apply the filter to the category (F2) field to include some it applies to my Total count field also, which is not what I want.

Example - F1 = Total 10 count

F2= A, B, C, D, E, F, G, H, I, J count 1 for each

when I filter out: A, B, C, D, E

F1 shows 5

F2= F,G,H,I,J

I want the total to show 10 along with filtered out letters... Is there a way for it???

Thanks,

Garima

• ###### 1. Re: Filter shouldn't apply to more than one field on the same work sheet

Garima,

You could make F1 a FIXED LOD calculation so that it ignores your filters in the order of operations. Either wrap the count in brackets { }, which means that it is fixed at the highest level of granularity or make a traditional fixed calculation by including dimensions of interest.

e.g. {FIXED [Letters] : COUNT(F1)}

-Wesley

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Filter shouldn't apply to more than one field on the same work sheet

Create a separate calculated field to consider only the 5 letters you need, and then use this field for the filtered data count. You don't need to apply any filter to the worksheet then.

• ###### 3. Re: Filter shouldn't apply to more than one field on the same work sheet

Thank you for replying Wesley. I came up with this Calculated field:     {FIXED [Category]:COUNT("A,C")}

When I applied it to Filters it gave me numbers like 64, 126. I did "All" for the filter. Yet I applied my Category filter and it changed the Total. Anything I missed in this?

• ###### 4. Re: Filter shouldn't apply to more than one field on the same work sheet

Hi Hari,

Thanks for replying. I tried that. My filed looks like this :

[Category]="A" OR [Category]="C"

As soon as I put this Calculated field on the Rows shelf if gives True or false lines. If I exclude False, it impacts the total count which is not what I want.  Please correct me if I didn't get the right workaround.

• ###### 5. Re: Filter shouldn't apply to more than one field on the same work sheet

Can you please attach you workbook? It will be easier to provide a solution then.

• ###### 6. Re: Filter shouldn't apply to more than one field on the same work sheet

Garima,

Using Hari's logic you can create the result you want with an IF/THEN statement. Similar to:

IF [Category]="A" OR [Category]="C"

THEN 1

ELSE 0

END

Then you use this as a SUM.

Or if you want this to result in a count distinct of Category, then you could use this:

COUNTD(IF [Category]="A" OR [Category]="C"

THEN [Category]

END)

-Wesley

• ###### 7. Re: Filter shouldn't apply to more than one field on the same work sheet

Hi Hari,

I Used the calculated field and ID count on Rows shelf and did a Dual axis. Now, as soon as I filter out the "0" from Calculation1 it filters my ID filed also. I want ID to remain unaffected by the filtering and still filter the second field.

Thank you so much

Garima

• ###### 8. Re: Filter shouldn't apply to more than one field on the same work sheet

Hi Wesley,

I tried your updated solution. Same, when I remove the zero from my filed it filters down the Total count also, which I want to keep intact.

Thanks,

Garima

• ###### 9. Re: Filter shouldn't apply to more than one field on the same work sheet

You need to attached the packaged workbook (.twbx extension).

• ###### 10. Re: Filter shouldn't apply to more than one field on the same work sheet

Done!!

• ###### 11. Re: Filter shouldn't apply to more than one field on the same work sheet

Well, is this what you are looking for?

The only thing that I changed is removing the zeroes in your above formula, then suppressing the null values in the chart, and finally hiding the null legend value. You shouldn't use a filter as I mentioned right in the beginning.

The other change I made is in the special value marks for the above calculated field, as can be seen below.

Hope this helps.

• ###### 12. Re: Filter shouldn't apply to more than one field on the same work sheet

Thank you Hari... For now this works

• ###### 13. Re: Filter shouldn't apply to more than one field on the same work sheet

You are welcome.