I am trying to do a similar chart, let me know if you get any responses
Tableau is not a full-on reporting application so things like this get complicated. There are two ideas I have:
- Create the report as a dashboard, with as many views as necessary to get the calculations right, and calculated fields to get the two custom summary lines. Getting the tables aligned perfectly will take some work, but it's feasible.
- If you don't have too many combinations of Master/Detail, then create a measure for each, along with measures for the subtotals and grand total. Create a crosstab with Measure Names on the Rows and manually sort the Measure Values so everything is in the right order. However, any new Master/Detail combinations will have to be manually added as new measures, so I'd tend to go for my first suggestion.
I haven't thought hard about it - but it sounds as if it might be possible to do something along the lines of the "top N plus other" thread that got a lot of activity a year or two back. Obviously there would be a lot more calculated fields, but the principle sounds the same.
If I understand your question correctly, it sounds like what you want is mainly a group. You could have a calculated field like
"M" + [Master Code] + "D" + [Detailed Code]
The make a group with the following together
M2D1, M2D2, M2D3
Hide what you don't need.
Put that sucker on the rows shelf (the group that is).
For the grand totals with exemptions, try a calculated field as Richard and Jonathan suggested.