Is there a way to dynamically change one set of buckets (row labels) for another? RRS feed

  • Question

  • Hi all,

    I've got a powerpivot that pulls total sales information. The bigwigs want this info bucketed in different ways: by country, by source, and 3 or 4 other things.

    The file was originally given to me as 6 pivot tables, each with the same aggregations and columns, differing only in which set of buckets are used to break the sales data into.

    I would like a widget that is LIKE a slicer, that contains each of the row-label bucket groupings for each of those 6 pivot tables. When the user clicks on one of them, it groups the sales data according to THAT choice of buckets.

    Please note - that this is not *really* a slicer, as no data is actually being filtered out - the totals remain the same, just the buckets they're being split out into are changing.

    This way I could have just ONE pivot table, and the result would be a much cleaner user experience.

    Is this possible?

    Thanks for any tips,


    Thursday, October 25, 2012 3:29 PM


  • 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(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 //

    Tuesday, November 20, 2012 7:12 PM