• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding measure headers as a slicer for a pivot table

C

Colin Mannion

Member
Hiya.

I have a pivot table and am using slicers. However, the option doesn't appear to exist to include some of the measure headers from the base data table as a slicer option.

Is there some simple code I can use which will enable me to add these measure headers as a slicer?

Thanks in advance.
Colin
 
Hi ,

Would it be possible for you to upload your workbook with the data and the pivot table in it , and then explain what you want the slicer to contain as the slicer items ?

Narayan
 
Hi Narayan

Here is the file.

What I am hoping to do is to add a slicer which has the measures in it, as opposed to having to pull them from a drop down. Hope this makes sense.

Volume
Turnover
Gross Profit
Marketing
Profit

Thanks for the help.
Colin
 

Attachments

If I've got what you want right, take a look at the attached, on sheet Pivot2, at cell N7, a pivot table and slicers added. Is this what you meant?
You could write code to do this but I wouldn't easily be able to mimic a slicer and it would be time consuming. What I did instead was to re-arrange your source data as in Sheet1. This was faster than writing code. I used Power Query to 'unpivot' your data, although it wouldn't be too hard to write a snippet of vba code to do it instead.
 

Attachments

Thanks for this p45cal - I like the solution, but the data download format isn't something I have control over - it's a standard across multiple retailers which they can all deliver. Will see if I can get it changed though, although that would make the data file 5x longer which may then present it's own challenges :-)

I have just managed to turn up a solution which looks promising - see link below:

https://sites.google.com/site/e90e5...lue-fields-for-your-pivot-table-or-powerpivot

Cheers
Colin
 
Hi p45cal

I did have that file from the link but was struggling to get the solution proposed to work - tried for better part of a day to sort it out but couldn't. I thought there might be an error in it or it might not work with the version of the version of Excel I have, so I used the same data to see if anyone had an alternative solution. I decided this morning to give it another go and did get it to work. Didn't want to look like a plonker (not succeeded on reflection) so offered it up as a solution so others who were working on it would have some reward for their efforts.

I do appreciate the time you invested in this issue, and hopefully providing you with a working solution means you will have something you can use in the future.

Appreciate this might not be a conventional approach, and apologise sincerely if this has p*ssed you off.

Regards
Colin
 
It's an ingenious solution. The code can be changed a bit
to simplify:
  • not to need a named range called 'Choice' (so the secondary pivot table doesn't need to be tied to any named range)
  • not to require the ptmain or i variables (although it does need cll instead of i)
  • to make use of an already established object variable, Target.

From:
Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptMain As PivotTable
Dim pfMeasure As PivotField
Dim i As Long

On Error GoTo Errorhandler
Set ptMain = Worksheets("Pivot").PivotTables("PivotTable1")
For Each pfMeasure In ptMain.DataFields
  pfMeasure.Orientation = xlHidden
Next
i = 0
Do While [choice].Offset(i, 0).Value <> ""
  ptMain.AddDataField ptMain.PivotFields([choice].Offset(i, 0).Value)
  i = i + 1
Loop
Exit Sub

Errorhandler:
Debug.Print Now(), Err.Description
End Sub
to:
Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pfMeasure As PivotField, cll As Range

On Error GoTo Errorhandler
With Worksheets("Pivot").PivotTables("PivotTable1")
  For Each pfMeasure In .DataFields
    pfMeasure.Orientation = xlHidden
  Next
  For Each cll In Target.DataBodyRange.Cells
    .AddDataField .PivotFields(cll.Value)
  Next
  Exit Sub
End With
Errorhandler:
Debug.Print Now(), Err.Description
End Sub
 
You must log in or register to reply here.
Back
Top