I'm confused as to why Totals would not be a viable solution for you? Totals do change as you start filtering data.
Table calculations can also be pretty powerful when you get the "Compute Using" logic correct.
To create a custom category you could also do:
if [Category] = "There is no way in heck this will aggregate anything but total" then "1" ELSE "All Categories" END
1 of 1 people found this helpful
To the best of my knowledge you can't get an additional category without modifying your data structures. There are no rows in your data set with [Category] = "All Categories", and creating a new calculated field will only allow you to reassign existing rows, not create new ones.
If I understand your issue with totals correctly (please correct me if I am wrong), the problem is that you want to use [Category] as a quick filter, except that when you select "All" you want the data to automatically roll up categories as opposed to displaying all categories. If that is a case, there is a workaround. Create a new "lookup" data set that maps the categories displayed in your filter to what you want in included the values. So for your example above, you would have 2 columns and 6 rows:
[Filter] / [Category]
"All Categories" / "Furniture"
"All Categories" / "Office Supplies"
"All Categories" / "Technology"
"Furniture" / "Furniture"
"Office Supplies" / "Office Supplies"
"Technology" / "Technology"
Then either join (if your data set is small enough) or blend this new data set with your original data set on [Category]. For each row in the original data set, you should now have a duplicate row with [Filter] = "All Categories".
I think you are understanding me correctly--the end result will basically allow the user to see it either way (i.e. either displaying all categories side by side, or rolled up)
I'm weary of using a blend to duplicate all data, as my dataset is quite large already (extracts are already taking ~2 hours without duplicating). I think we're getting closer, but wouldn't like to risk the performance hit.
Totals won't work for me because my real workbook contains many measures (including table calcs etc). These calcs are distinct from each other and, unless i'm misunderstanding you, a "grand total" across calculations wouldn't yield anything meaningful.
Is Analysis -> Totals -> Show Column Grand Totals not sufficient for your purposes?
I've tried an approach similar to what Matt mentioned to get counts, but not sure how to do this for a sum of values. I'd like to look at this later if someone else doesn't get to it first.
1 of 1 people found this helpful
Your performance concerns probably rule out a join (that would double the size of your data set), but blending occurs post-aggregation, so I don't think you would take a performance hit.
With that said, if you want to avoid both joining and blending you may need to look into solutions involving multiple tables. Specifically, you could have a dashboard with one table containing data at the [Category] level, and another table with rolled up data displayed directly above / below. Clever use of filters and/or using a helper sheet for the filtering might allow you to replicate the functionality you are looking for.
I had forgotten that blending was so late in the order of operations! I'll give that a go, thanks.