I think of what Tableau does as taking a "canvas" of your raw data and then transforming that into another "canvas" of the visualization, and that table calculations create intermediate canvasses that can have different aggregations and sorts. Mastering table calculations requires knowing Tableau's rules, and being able to have your own mental models of what those intermediate canvases look like, and that is a constant process of trial, error, and refinement.
Tableau does some internal caching and re-use of settings, and when you are using nested table calculations that are used inside other calculations - such as Market Share (% of Total) which is used in % Point Change of Market Share, which is then used in Colour % Change, you need to build slowly and any time an intermediate calc changes, it's necessary to re-check what's going on with the higher-level calcs, because they are mostly but not totally independent of the settings of other calcs. I'm pinging Ross Bunker on this one, in case he has the time to give an explanation.
The goal is to have the Market Share (% of Total) to be computed along Parent Publisher (partitioning by Period Weekname), with the higher-level % Point Change of Market Share to be computed along Period Weekname, partitioning by Parent Publisher, and retain the nested Market Share compute using settings. That's a pretty normal set of table calculations, where we'd want to aggregate along one dimension for a measure (market share) and then aggregate along a different dimension to calculated the difference in market share from one week to the next. Then the Colour % Change measure will use the same settings as the % Point Change measure.
Here's the fix:
- Right-click on the % Point Change of Market Share green pill, choose Edit Table Calculation, and in the dialog that comes up set the Compute Using for % Point Change to Period Weekname, then in the Calculated Field drop down choose Market Share (% of Total) and set that Compute Using to Parent Publisher. (You won't see any change in the view, which is confusing and what I'm hoping Ross can comment on as to why this is. I set this up in the "Setting % Point Change alone doesn't change anything" worksheet in the attached)
- Then right-click on the Colour % Change blue pill on the Color Shelf, choose Edit Table Calculation, and in the dialog that comes up do the same steps as above. The view will change to have correct values of Market Share and correct colors (seen in the "Fixed - setting both works" worksheet).
The strange bit is that if you do step 1 above, then take Colour % Change off the Color Shelf and out of the view (or vice versa), you will see the correct result for % Point Change (see the "No Colour - see correct value" worksheet). Somehow the settings of one calc are influencing the other, and I believe something just like that is going on when you drag other pills into the view as in your "Working Solution" worksheet.
In terms of avoiding this in the future, my suggestion is to only bring one new calc at a time into the view and make sure it's using the right Compute Using settings, particularly any nested Compute using settings, and if a calc at a higher level of aggregation is failing then double-check to make sure the lower levels of aggregation are all correct.
Market Share Error jtd edit.twbx.zip 322.6 KB
Wow - thanks for your thorough response Jonathan. Great to see the Tableau 'community' in action, really helpful. I will try to implement today. Looking forward to Ross Bunkers explanation also!