Pivot Table Slicer on Values Field [SOLVED]

[jaykilleen]

Hi, I have been playing around with the Slicer feature for Pivot Tables. It is awesome. One thing I can't figure out is how I can slice my data based on the values field. For example, I could use the slicer to select the store and have my chart return the quantity of product sold as the value field. But what if I want to quickly switch to gross margin, revenue or count of products sold. I know the slicer deals with values within a category but is there a way to make it easily switch the category used as the value field?


I have started to build a macro to switch them out but am hoping there is an easier way using the slicer.


Any ideas would be greatly appreciated

 

[jaykilleen]

I have figured out a Macro to do this that calls a Macro that filters the data fields and then inserts the field I want to use.


Sub ValueAsGM()

'

' ValueAsGM Macro

Application.ScreenUpdating = False

Call DataFieldsClear

ActiveSheet.PivotTables("SalesHistoryTable").AddDataField ActiveSheet. _

PivotTables("SalesHistoryTable").PivotFields("Gross Margin"), "Sum of Gross Margin" _

, xlSum

With ActiveSheet.PivotTables("SalesHistoryTable").PivotFields( _

"Sum of Gross Margin")

.NumberFormat = "$#,##0"

End With

Application.ScreenUpdating = True

End Sub


Sub DataFieldsClear()


Dim PT As PivotTable, ptField As PivotField

Set PT = ActiveSheet.PivotTables("SalesHistoryTable")

For Each ptField In PT.DataFields

ptField.Orientation = xlHidden

Next ptField

Set PT = Nothing

End Sub


It would be nice for the Slicer to be able to do this but on second thoughts it seems like a completely different way of doing it.