Hi Sff -

This sounds like a requirement that would probably be more easily fulfilled with another tool. Dynamic groups in SSRS would be one option.

Regardless, I'll address your question from a PowerPivot/Excel perspective since that's what you asked. In general, since the values you would be slicing by on an axis need to be columns in the model, they can't have logic that reacts to selections
in the client / pivot table. And you can't put a calculated measure with conditional logic on an axis. So with a simple model, this won't work.

However, there is a way to handle this with a different modeling approach. The solution I'll describe here requires that all of the buckets that you'd like to slice by are related to a single dimension / table (e.g. customer) that is directly related
to your sales table. Let me know if that's true in your case. Specifically, in your example above, are both country and source and the other buckets you need related to a single entity like customer? If so, you can accomplish the flexible
filtering you are looking for with a Many-to-Many model that creates multiple groupings of the customers.

Basically, each sales row is related to a single customer. But by using a bridge table off of customers, you could create multiple buckets to group the customers by. And then at report run time (in the pivot), the user would simply filter to
a single set of buckets to see sales sliced that way. And they can easily flip back and forth between the types of buckets. The end result would function like this.

Here's the sample model:

And here's a sample pivot that shows what this would look like filtering down to one set of buckets (in this case Country):

And here's the key part. The calculated measure "SumSales_M2M" that takes into account the bucket type that's chosen in the bucket filter (GroupID = "Country"):

=CALCULATE([SumSales]
,FILTER(Customer
,CALCULATE(COUNTROWS(CustomerGroup)) > 0
)
)

There's a more detailed write up of this Many-to-many modeling approach and the calculation logic in PowerPivot in Russo and Ferrari's Many-to-Many Revolution paper
here. Can also use a slightly different calculation using SUMMARIZE in PowerPivot v2 to simplify things. Let me know if that helps and if you need more details.

Brent Greenwood, MS, MCITP, CBIP

// Please mark correct answers and helpful posts //

http://brentgreenwood.blogspot.com