Sheet 1 uses a table calc to do a LOOKUP to get whatever you pick from the list of items in that dimension.
When you use a table calc as a filter, it still leaves the underlying table intact on the sheet, and just DISPLAYS what was requested, rather than eliminate actual rows from the table as a quick filter does. thus, the TOTAL line still adds up whatever is in the entire table. Pretty powerful, but it can also be frustrating if you don't get what's happening under the hood.
Sheet 2 uses a LOOKUP as well, but rather than select from a filter list, it uses a parameter value to do a CONTAINS operation.
One caveat for this method. If you type in something that gets NOTHING from the table, you get no rows at all, and therefore no total.
Sheet1 is VERY close to what I'm trying to do, but it appears that in order for the Grand Total line to remain intact, the bottom value on the filter needs to be selected.
is the "Max" mandatory?
Not sure what you mean by your first statement. If I deselect "red cars" from the filter, I still have a grand total of 6.
As for "MAX", there has to be some sort of aggregation of the field inside the LOOKUP function. MAX. MIN. Both work. A lot of times you can use ATTR for a dimension like this, but for some reason that one is adding a (star) to the filter list. So I just went with MAX here.
It turns out that the issue I was seeing was an anomaly in the data I'm pointing to, and not a problem with your solution.
Thank you for your help.