Slicer for Values in Pivot Tables

[Akakaboto]

Hello,

I have a PP-table where I have a lot of varibles in the Row Columns and YearMonth in the column labels.

Sometimes I am interested in having both Amount and Quantity information in the Value field, other times I am interested in just looking into Amount or Quantity. Instead of having to add and remove the the variables from the pivot table I would like to be able to select it in the same way as I do for normal slicers. is this possible, and if so, can anyone direct me where to find info on how to do it.

 

[Kazlik]

Look at the SWITCH function, this article has one example and links to an example Rob did over at PowerPivotPro.

https://eriksvensen.wordpress.com/2013/08/06/use-slicers-to-select-the-facts-in-a-pivot-chart/

 

[scottsen]

The only way I can think of is to have a "Disconnected Slicer", that changes your calculations... such that if "Amount" is sliced, your [Amount] measure works per normal. And if it is NOT sliced, it just returns BLANK().

 

[SpillerBD]

I would set up macros for each case and apply to "buttons" in the same area as the slicers.

 

[Akakaboto]

Thanks for the info guys. When I get back to work on monday I will try it out. That article looked interesting. Need probably the method that requires least capacity as my data is pretty heavy with one table of 700 000+ rows and 20 columns. Too much calculations will probably make it very slow when filtering.

 

[SpillerBD]

Sometimes its just easier to have multiple pivot tables on the same data. It doesn't take much more headroom for the additional tables...
I once had a pivot table from a linked Cube data set. Slicers on that pivot, but formatted so I could actually use that table as a data source for another pivot table (yeah that sounds crazy.) There was an additional column associated with that table. Then I had two pivot tables that would update from slicers affecting the source Pivot table.
700k x 20 -> Oy vay!