I'm able to get the correct values, but applying the action is always going to filter the branch codes down in the line chart to a single value, so I'm just not sure how to connect all the pieces; if you could live with two charts, I could make it happen, I think, but I realize that is not ideal for what you want.
I'm just not sure how this can be accomplished with the necessity for a Table Calc on the Average Branch Sales. I thought I could at least get the values with a reference line, but that's not returning accurate results either.
I'm going to ping in some folks who seem to like a challenge on these forums (although one of them could probably have a solution very quickly), and see if they have time to take a look at your problem--hopefully they won't get too overwhelmed with our long back and forth discussion here.
This result was produced with the Average Branch Sales Density Calc, set to compute using Branch Code (which you probably already figured out--I did make Fin Week a discrete date dimension to return this value, but again, as soon as an action filter is applied, the calc can only reference one branch and so both axes return the same result)
I'm sure there's a solution; I sure wish I could figure it out!
I'm starting to feel a bit pessimistic about this but hopefully one of the guys you pinged may have some insight. It occured to me that this idea: http://community.tableau.com/ideas/1604 would solve this problem. I actually voted this up for completely different reasons months ago. I wonder if it will make Tableau 9?
I hope there is a solution though as this particular dashboard is just the thin end of the wedge. I have two other projects coming up that I would like do implement this sort of thing into but in a more complicated way (way more branches and averages of specific groups such as region, store types etc.)
I've been playing with this a little.
I'm not 100% clear on the limitations of dashboard action filters, but I believe the big one and the one causing you problems here is that the target can't be an aggregate, which means that
1) the target can't be a field from a secondary data source and
2) the target can't be a table calculation.
Both (1) and (2) are aggregates.
In your example, you have a bar graph with branch and average density for that branch.
You want a line graph with
1) density for the branch that is selected (via dashboard action) in the the bar graph,
2) a window_avg() of all the branch densities to provide context.
The issue is that (2) needs to be done at the primary data source, since you need to calculate the density for each branch and feed this into window_avg(). But you can't apply a dashboard filter action to a secondary data source.
I don't see an easy solution, so you might (re)consider a couple of alternatives.
1. Use a parameter
You've probably already done this, and if you haven't it's relatively straight forward to create calcs that display a value only if the dimension == the selected param. If you block your users from sorting, you could also add the radio buttons next to the branch IDs.
Of course parameters are less than ideal when the list of branches might change, which seems like it could be the case here.
2. Use small multiples
If you're only looking at a couple of dozen branches at a time, then displaying a small chart for each has several advantages. For starters, it's by far the best way to compare performance for multiple stores. The alternative with click-select forces viewers to remember what they saw. If they're looking for something specific, fine, but if they're trying to understand trends / relationships / patterns, it's a somewhat poor design (and one that's too common among Tableau examples --- interactivity is not always good IMHO).
Of course this is also quite easy to do (relatively speaking).
Both 1 and 2 do not require any duplication of data sources or anything very fancy at all.
If you really want to solve your problem by clicking to display, then I think you need to develop a data set (or data scaffold) where filtering by the primary branch chain still gives you a complete set of rows (at the branch level) for the branch density and window_avg() calcs. Of course this is possible (and probably even with the JET custom SQL), but it's a bit ugly and unwieldy.
Hope this helps.
P.S. One other point that I wonder didn't cause you come confusion is blending where the primary data source is not "domain complete" for the secondary. In one of your examples, you were blending on FinWeek, but had deselected Branch Code in order to calculate a WINDOW_AVG() across all branches.
The potential problem is that you needed the link on FinWeek, which means that if you select the left-most branch 3314, you will only be including rows where FinWeek exists for 3314. At a minimum this causes your x-axis range to shift depending on what branch is selected, which can be confusing for the viewer. In other cases, you might be excluding values from the non-linked branches.
Density Mockup_JimW.twbx 122.4 KB
Boy, I wish I could LIKE this type of thing over and over. I assumed alternatives might be necessary here, but was not exactly sure what to suggest. This is very helpful.
Since using a table as a data source was mentioned, I assumed pre-processing some of this data before it gets to Tableau might help get at a solution to meet the requirements, but as you (Jim) have said, this may not be an ideal solution either, depending on the organization, Kes's role in that org., and a number of other factors.
This gets at one of the things I struggle with most in Tableau -- getting Action filters to work in a lot of situations can be difficult for me, so I am often asking for data from our ETL folks that makes this much easier in Tableau. But even then, I don't always know what to ask for, and we have to worry about performance, usability, and all the other aspects of a wonderful user experience.
Thanks Jim, for providing some options. I sure wish you had been at TC14!
Thanks for a very comprehensive summary.
The parameter solution had occured to me but as you correctly guess the list of branches is far from static. Given that dynamic parameters is the highest voted for request in the ideas section by a massive margin I'm hopeful we'll see this in Tableau 9.
I had also considered doing custom sql to do precalculate the value and present it to Tableau at row level. It's someting I don't want to resort to as we're trying hard to use live connections to the data warehouse and not end up with a mass of report specific tables that we then need to schedule for refreshed, which could become difficult to manage in the long term. (We don't use data extracts except where users specifically need offline access to a report for a number of reasons.)
I quite like the "small multiples" approach you suggest, although I can't help thinking that whether you put the branch on the x or y axis it always feels slightly too squashed. A grid would be ideal. I'm wondering if there's a way to create row and column values from the branch code to do just that...
I didn't know that the target for filters could not be an aggregate so thanks for explaining why that wouldn't work. It's useful to know.
I have also learned during this that Tableau blends data before performing table calcs, which is again good to know and furthers my understanding as to what goes on under the hood.
Finally I'm quite relieved to discover that this problem is only an issue because of the use of table calculations and that the duplicated data source method works for "regular" calcs. Given how much the business wants to see branch performance vs average it is good to know that for the most time there is a solution, and one that end business users can apply without having to know about databases - which business users shouldn't be worrying about.
I quite like the "small multiples" approach you suggest, although I can't help thinking that whether you put the branch on the x or y axis it always feels slightly too squashed.
Creating wrapped panel or grid panel charts in a single worksheet is harder than it should be in Tableau.
An easier path might be to split the branches into two or three groups based on SIZE() --- the number of branches after filters have been applied --- and then have two or three columns. Each column would have a worksheet for the bars and another for the line charts.
Another approach is to place the bars beside the line graphs --- basically larger sparklines. (I left the y-axis labels on the graph above, but they are not really necessary if you label at least two points on each line --- I used start and end, but you could also use min/max. Or start/end, min/max --- there are a couple of examples here: Two Tips for Meaningful Sparklines in Tableau | Tableau Public).
Definitely #3 for me Jim.