If, say, we had a table with dimensions Customer and Store, and expression sum(Sales), then we could get the total from the pivot table with this:
sum(aggr(sum(Sales),Customer,Store))
That's overkill for our specific case, because a simple sum(Sales) gives us the same total, but it's just an example of the syntax for totalling values in a pivot table. In this case, the very first pivot table in the example. Now we're using calculated dimensions in our real pivot table, so I tried literally inserting those calculated dimensions instead:
sum(aggr(sum(Sales)
,aggr(if(rank(sum(Sales))<3,Store),Store)
,aggr(if(rank(sum(Sales))<3,Customer),Store,Customer)))
No luck. It returns zero. Perhaps I just can't use an aggr() in an aggr() this way. But even if it worked, I figured it could be simplified by thinking about it a little more critically, so let's do that.
First, our stores are fixed in a way that our customers are not. We should be able to set them with set analysis somehow. This works for that part:
sum({<Store={"=rank(sum(Sales))<3"}>} Sales)
The customers differ by store, so can't be done in set analysis like that. We'll probably need to aggregate by customer within store:
sum(aggr(sum({<Store={"=rank(sum(Sales))<3"}>} Sales),Store,Customer))
That gives the same answer, just the sales for stores A & B, so so far so good. If I can't do the customer condition with set analysis (since a set is evaluated once for the chart, not once per store), then maybe I can do it with an if():
sum(aggr(if(rank(sum(Sales))<3,sum({<Store={"=rank(sum(Sales))<3"}>} Sales)),Store,Customer))
Took a few tries to get it right, but yes, that works. For consistency with our selections, I should probably use *= in the set analysis. And of course we need to divide by sum(Sales) to get our percentage:
sum(aggr(if(rank(sum(Sales))<3,sum({<Store*={"=rank(sum(Sales))<3"}>} Sales)),Store,Customer))/sum(Sales)
And here's an unexpected benefit of this approach - calculated dimensions tend to be slower than expressions in my experience, and this expression should give us another way to solve the ORIGINAL problem of the pivot table, but without using calculated dimensions. The reason is that its structure is an aggr(...,Store,Customer). That expression mimics a table with those two dimensions, which is what we wanted in the first place. Should be able to just turn that expression into a pivot table:
Dimension 1 = Store
Dimension 2 = Customer
Expression = if(rank(sum(Sales))<3,sum({<Store={"=rank(sum(Sales))<3"}>} Sales))
Unfortunately, no, it doesn't seem quite right for a pivot table. It requires our Store dimension to come before our Customer dimension, or it gives the wrong results. That could be OK if we turned off pivoting. The other problem, and I'm probably just missing something, is that it's returning all rows, even when calculating null or 0, even though I suppress zero values.
