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

77bn4fn4i

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
1626159709638.png

1626159741624.png

1626159760916.png
xcel
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,550
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Ok makes sense, I am struggling to make it work another way? Do you have any ideas?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,005
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is there anywhere I can upload data?
 

77bn4fn4i

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Just because I have never used power querys before and not sure exactly what you mean
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,005
Office Version
  1. 365
Platform
  1. Windows
  • 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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,550
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,143,623
Messages
5,719,801
Members
422,245
Latest member
Kebad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top