9 Replies Latest reply on Mar 20, 2016 10:01 AM by Francesco Dall'Asta

# Force filtering in the calculation

Hi,

Is there a way to force filtering for some values in a calculation independently from the viz filter or way the data are broken down?

For example seeing the Sum of sales of a given set of customers and at the same time the normal sum of sales broken down by customers, to have a comparison.

In the calculation of the sum of sales for the given group of customers there should be something that "forces" the calculus to be for those customers..

Thanks!

• ###### 1. Re: Force filtering in the calculation

If you plan to show this data on Dashboard, then I would suggest you to create 2 different sheets and put them on the same dashboard.

It would be easy to do as well as will look good to end users. I have done similar dashboards with Cards layout and my users love it.

• ###### 2. Re: Force filtering in the calculation

I agree with you that showing in a dashboard would be the best solution. Unfortunately I need to embed the filtered data in the view in order to compare the two data in a computation (ratio of the first data on the other).

Is there a way to do it showing them separately? Thanks!

• ###### 3. Re: Force filtering in the calculation

Hi There!

Can you give some more context to what you are asking for? I am not sure I fully understand what you mean. Can you create an example with the superstore example that ships with Tableau? It will help us better help you!

Pooja.

• ###### 4. Re: Force filtering in the calculation

It might be possible.

It would be great if you can share meta data sheet and/or the the exact view you are trying to look form.

If you can create kind of layout for the view you are looking for in excel, it will help.

• ###### 5. Re: Force filtering in the calculation

Hi Satish, Hi Pooja!

Thanks for your help. I prepared a tableau packaged workbook with the superstore data that better explain my question.

In the dashboard you'll find the data and a comment explaining the calculus I wanna do and an excel screenshot of the expected result.

I really hope you guys can help me!

Thanks a lot!

Francesco

• ###### 6. Re: Force filtering in the calculation

Please, find the attachment in my previous comment

• ###### 7. Re: Force filtering in the calculation

Hi Francesco,

I am still not sure I understand but I attached the workbook based on your explanation. My numbers are kind of different than yours but maybe you can modify them based on your need. Let me know!

• ###### 8. Re: Force filtering in the calculation

Oh sorry! I think I get it now. First you are going to want to create a custom date for the year datepart of ship date, so you can use it in the dimension declaration of the FIXED expression. That can be found here:

In the pop up select year and date part radio button.

Fixed Ship Date Calc: { fixed [Ship Date (Years)] : sum([Sales]) }

And then Expected Result Calc: sum([Profit]) / sum([Fixed Ship Date])

Also initially the values of expected results will show up to be zero, so you will want to go to the default number properties of that calculated field and change the decimal places to 2 or 1.

Hope this helps!

Pooja.

1 of 1 people found this helpful
• ###### 9. Re: Force filtering in the calculation

Hi Pooja,

Thanks a lot, I think you hit the point!

The only remaining problem is that in my workbook instead of the normal measure "Sales" I already use a fixed calculation on Sales because of the shape and structure of my dataset.

If I add another dimension in the FIXED computation the expected result is not right anymore: {FIXED Dimension 1 , [Ship Date (Years)] : sum(sales)}

In other words keeping fixed two dimensions seem to mess up things in the calculus. Maybe I should use Exclude or Include in addition..?

By the way your answer was correct given my example and was helpful for me to understand where I was wrong. Thanks!

Francesco