I could see two separate-but-related issues with the calculations as you'd laid them out.
- Country was a filter, so anytime you filtered on Country then it would filter all results and your overal total would be off.
- There was nothing different in the attrition rates calculations to filter by country
What I did was the following:
- Created a parameter for country and included an (All) value for all countries
- Created calculated fields for FTE Q4 2011 and Attritition to filter by the parameter
- Created a new Attrition % for Country calculation using those fields
- Duplicated your Sheet2 view and put in the new calculations and parameter control
- Set the New HL2 desc filter panel to show Only Relevant Values
The Sheet2 view didn't show anything for HL3, however you could add that to the view if you want and put a quick filter on it with show Only Relevant Values and that should work for you to have the dynamic update you are looking for.
Let me know if this works for you.
Thanks very much for helping out. Somehow this is not what I want. Let me explain again.
There are 4 columns in the data(attached), for Example if we filter “Category” from the first column and then filter for “Food” in the second column, we get all the countries where food is present.
My query is, in the above case is it possible to show the sum of all the value of all the countries where food is present (not just sum of rows where food is present in 2nd column).
ie in the example, after filtering “category” & “food” if Argentina is selected in the 3rd column, sum of value will be 10. But we need to show that as 986(sum of values where 1st & 2nd column is not filtered). Also if there are more than one country then sum of all people in these countries.
Now using this total value for these countries I will calculate Country attrition( i.e attrition in these countries / total FTE in these countries)
While my 'normal attrition' will be what is being shown by the normal filter. (i.e attrition in Food / FTE in Food).
I know I am not very clear, please bear with me.
Sample file.zip 483.3 KB
I believe what you want can be done in Tableau, just in a different way.
I think my confusion has been around the use of the word "filter." In Tableau, a filter explicitly includes or excludes data from being available to all calculations. There are a few idiosyncracies due to the order in which Tableau applies different filters (context filters before regular filters, filters on table calculations are applied last - see this comment by Joe Mako for details: http://community.tableau.com/message/139603#139603), which can sometimes be a hindrance or an advantage.
In any case, the way I'm interpreting your use of the world "filter" is that it means including or excluding data for different calculations in the same view. In that case, you generally won't use a filter on the Filter shelf unless you want to include/exclude data for all calculations. What you would would do instead is use various aggregate and table calculations to effectively do the filtering, and control those calculations through either parameters or action filters (if you are using dashboards).
This is what I did with the Country parameter in the posted workbook, so you could get Country-specific results while also showing totals for all Countries. You can create a parameter for the values of H2 in the workbook that will then be used in the attrition calculations.
Does this make sense? Hopefully I've given you enough information so you can proceed on your own, if not let me know and I'll see what I can do.