The good news is, you're not missing anything obvious! Tableau grand totals and subtotals use whatever the aggregation that is on the measure. However, there are some ways around this.
By creating a calculated field similar to the following:
IF FIRST()==0 THEN
Then, create a duplicate of your market field (right click on it in the data box and select Duplicate). Place the duplicate on the level of detail shelf. Place the above calculation on the view, right click and select Compute Using-->market (copy)
Hope this helps!
Thank you for such a quick reply and I know you've seen something similar to this probably many times. I'm following through your example (I understand the formula). I have duplicated the market (not originally named 'Market (copy)' ) but I am uncertain where the calculation gets loaded (it is named avg95_sum - not that that matters).
I have enclosed my screen on Sheet #2 of where things are. We have to be close (actually it is all you)!!!! I'm not certain about the compute using reference (yes I know how to reference it and need to figure out what that is doing and will research to better understand).
I have loaded the copy on the detail shelf. If I put the calculation on the column I get graphics, and as a discrete it isn't quite right. Is it possible to keep the text of the table, and have the calculation come up as a column at the end (much like a grand total does)? I ask because I reference this column to determine appropriate coloring of each cell whether it is > than a % of the grand total (which is the sum of average - the last column).
Sorry you are having to drag me through the answer and thank you very much!!! The results from Tableau are getting a great response.
TableauSample.xlsx 226.0 KB
With a clearer head I worked through your example and it is functional (of course). I moved the formula into the text mark and it worked. I need to think about the recompute with the copy, but thank you !