7 Replies Latest reply on Aug 21, 2013 7:09 AM by Pedro Machado

# Combine a chart with single data based on filter and the aggregate without filter

So I have data which includes different countries and different categories and what I want to do is to create a bar chart with two bars. The first bar contains the filtered data on both country and category. The second should should only be filtered on the category filter so that I can compare the individual country against all countries based on the category filter. I should add that I have quiet a few countries and categories in the filters to choose from.

Any ideas on how to solve the problem? Is there a way to create a calculated field that ignores a filter?

Best,

Markus

• ###### 1. Re: Combine a chart with single data based on filter and the aggregate without filter

Hello Markus.  Can you post your workbook?  If I understand your question correctly, this is possible by using calculated fields instead of filters.

• ###### 2. Re: Re: Combine a chart with single data based on filter and the aggregate without filter

I cant post my workbook since its sensitive data but here is a really simple mockup containing the structure I have. To further explain my data the country filter is a global filter whereas the category filter only applies to the graphs.

I also think it can be solved with a calculated field but I am currently blank as on how to actually implement it.

• ###### 3. Re: Re: Re: Combine a chart with single data based on filter and the aggregate without filter

Markus,

I think this is what you are looking for. Let me know.

Pedro

• ###### 4. Re: Re: Re: Combine a chart with single data based on filter and the aggregate without filter

Thanks a bunch Pedro! Sadly I have problems with implementing your solution. First of all my country list contains 1000+ entries and the data is a bad combination of letters and numbers.

Do you have any idea on how to keep my global filter (country in the example) and SUM the calculated field on all data in the category filter which should be applied to both data? Is there a quick way to just ignore one filter in a calculated field?

• ###### 5. Re: Re: Re: Re: Combine a chart with single data based on filter and the aggregate without filter

Hi Markus,

I am not sure I follow what the problem is.

The country filter was applied to a table calculation instead of the country dimension so that the calculation is done across all countries, prior to filtering.

Country Filter = LOOKUP(ATTR([Country]),0)

If you use the country dimension to filter, Tableau will remove the data for the countries you are filtering out before calculating the category totals and this is not what you want.

Since you are comparing against category sales, you could add a category filter without affecting the calculation. In other words, if you don't want a category on the view, you can filter out the underlying data for that category because it won't contribute to other category totals.

I reattached the workbook with a category filter. Let me know if you still see issues with the approach.

Thanks,

Pedro

• ###### 6. Re: Combine a chart with single data based on filter and the aggregate without filter

So what I want to do is keep my current global country filter that is on my first dashboard and this filter is filtering all data connected to that data source. In my chart dashboard I want to display that globally filtered country and gauge its performance against all countries in my data set. This should then be filtered on my category dimension.

The issue I see with your approach is that I would need to apply a new filter which I want to avoid. It also had problems in recognizing some of my rows in the filter. I want to create a calculated field that is unaffected by the country filter so that I can still use my country filter as a global filter.

• ###### 7. Re: Re: Combine a chart with single data based on filter and the aggregate without filter

Markus,

If you filter out countries just dragging the country dimension to the filter shelf, the data for the excluded countries won't contribute to the category totals.

If you need the category total to include data for all countries (including the ones you are filtering out) and still use a "traditional" country filter, then it seems to me the only other way to do it is to create another connection to the same data source and blend in the category summary to the country view.

See attached.

Does anyone else have other ideas?

Thanks,

Pedro