What's the issue? The action seemed to work for me (it's set to menu, so I had to hover over a bar and then click the link to run the action).
The green line for Sales is missing on the graph when you view the graph from the Action filter. However it works when you select a Category or SubCategory on the Quick Filter on the Graph tab directly.
The green line for Sales is there, but it is the same as the Avg Sales per SubCat. Click on Sales on the color legend and you'll see that it is just hidden behind the gray line. The issue is to do with the Sub-Category filter field. Action filters don't take into account table calculations.
Not sure if there's a way around this--I'll have to think further on this one.
I've got one possible solution in the attached. The problem here was multi-layered:
- The workbook was set up to have the Graph worksheet use a table calc filter to choose a particular SubCat, while showing a table calc for the Average Sum of Sales per SubCat for that SubCat's Category, along with a Sum of Sales for the particular SubCat.
- The Dashboard was set up with a Filter Action to filter the Graph worksheet on Category and SubCat.
- As Tracy noted, Filter Actions only work on dimensions, they don't work on aggregates or table calcs. So the Graph worksheet was being filtered for a specific SubCat, which caused the table calc to return the same result because it no longer had all the SubCat's available to the computation.
- In cases like this I'll start thinking of a self-blend at a different level of detail by working with the linking fields. However, this won't work in this case because both meaures (Sum of Sales and Avg Sum of Sales perSubCat) require SubCat to be available, and the Filter Action would (again) filter SubCat for both measures.
- I think of Tableau's computations in layers - row-level, aggregate, and table calcs, then nested table calcs. The higher up the stack we go, the more limitations there are on what can be done with the computation (and greater complexities for implementation) vis-a-vis Tableau's other functions. For example, not being able to use Filter Actions on table calculations, or being able to partition a table calculation by the results of another table calculation. Therefore, a possible solution for these cases is to "push the computations down", i.e. make a table calc a regular aggregate, an aggregate a row-level calc. That's what I did here, I got rid of the need for the table calculation by doing the first level of aggregation in a secondary data source.
I created a new connection to the Superstore Sales data with Custom SQL that aggregates the Sum of Sales per SubCat/Category/Month. Then I made sure that the blend was going to happen on Category and Month (but not SubCat), brought in AVG(Sum of Sales) from the secondary, and created a new Filter Action. Now the primary is filtered on SubCat (there's no need to add Category to the Filter Action because each SubCat is in only one Category), and because the blend does not apply to SubCat the AVG(Sum of Sales) returns correct results.
Thanks Jonathan. Only problem with this is... in my actual data, each SubCat can be in more than one Category. Sorry i wasn't able to illistrate that with the sample data here.
Also, on the Graph tab I wanted to allow the user to select a Category and SubCategory that way they could change the view without having to go back to the Dashboard tab and use the Action Filter.
Good news: To have the Filter Action work in that case, I'm almost positive all you need to do is add the Category as another field in the Filter Action and it should work.
Bad news: Filter Actions and Quick Filters don't talk with one another. It's incredibly easy for a user to pick Furniture as a Quick Filter in the Graph worksheet, then go back to the Dashboard, then try to go back to the Graph for Technology, and get nothing because the view is filtering for Category=Furniture AND Category=Technology. Your original solution worked for the SubCat filter because you were using a table calc filter for SubCat, but if a user had picked different Categories on the Dashboard and Graph views then they can run into this problem. There are a few workarounds, like using global filters or parameters, that involve re-thinking the workflow so I can't give a more specific recommendation.