slicers for custom calculated field in excel pivot table/pivot chart

[77bn4fn4i]

I have a multiple calculated fields in my excel pivot table.

The field is in the values part of my pivot table and I sum them. I want to add a slicer to select them or not.
i.e. I want my slicer to select basically hide and show AA1,AA2,...,AA8 in the chart and table.

This is normally easy if the field already exists in the table, but because this is a calculated field I can not work out how to do it.
My data is attached.

Cheers

xcel

 

[RoryA]

Welcome to the forum.

That is not what slicers do. They filter values in a particular field, not whether or not a field appears in a pivot table at all. I think you're going to need code to achieve what you want, and if you want to use a slicer, you'll need a separate table with a list of the available fields in it.

 

[77bn4fn4i]

Ok makes sense, I am struggling to make it work another way? Do you have any ideas?

 

[Alex Blakenburg]

What about putting Power Query over the top. Transposing the column and putting your pivot table and pivot chart on that ?
You should then be able to use your slicer.

 

[77bn4fn4i]

Is there anywhere I can upload data?

 

[77bn4fn4i]

Just because I have never used power querys before and not sure exactly what you mean

 

[Alex Blakenburg]

• Convert your original data table into a proper Excel table.
• Give the table a name. I called mine tblData
• Data > From Table/Range (button on far left)
• You will now be in the Power Query editor
• If you see a Changed Type step after Source in the right hand pane - delete it
• Select your first 3 columns (ID to n)
• Transform > Unpivot Columns > Unpivot Other Column
• Click anywhere in the grid and hit Ctrl+A (select all)
• Transform > Detect Data Type (will be roughly below Add Column)
• Click on the icon in the heading of the Date column and change the type to date.
• Check the value field Data Type depending on whether you want to keep decimals or not eg Whole Number (no decimals), Currency (4 decimals), Decimals (max decimals)
• In the right hand side give the query a name it will become the output table name too, so pick something useful
• Home > Close & Load

You will then have a table that should work for a pivot and pivot chart and allow slicers

 

[RoryA]

Also please note:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: slicers for custom calculated field in excel pivot table/pivot chart
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.