Pivot table query

[gmazza76]

Good evening,

I have a pivot table feeding from data, but would like to know if uts possible to get the filters to restrict each other.

Ie. If I select town in filter 1, I want filter 2 to show the post codes that are in the data as an second filter, then to show another filter option in filter 3.

Sorry if this sounds strange but the informatation is setup this way in the data its feeding from

Thanks

 

[alansidman]

Please use XL2BB and upload a sample of your raw data and your PT and then show us what you mean. Tough to visualize what is not visible.

 

[gmazza76]

Apologies,

I am not able to upload a sample as my laptop is playing up, but I have been able to snip 2 pictures (1 of Data & 1 of the Pivot setup).
What I want to be able to do I can on the Data its self by filtering each column, but when I complete this via the pivot table it shows data that isn't relevant to the filter.

If possible for ease of understanding I don't want to show something in the filter below each one if its not relevant.
For instance if I choose JCB as "Unit Name" I would only expect to see "Location" as MCR1 & MCR2, but MCR3 is showing also and all the way through the filters

thanks

 

[sandy666]

to be sure: you select one filter and you want eliminate not relevant elements from another filter?

 

[gmazza76]

That's Right @sandy666, I have previously done something similar in a userform.

Surely there must be something similar I can do for a pivot table.

thanks

 

[sandy666]

I don't think so it is possible with standard Pivot Table only
Probably you'll need vba to do that

Unit Name	Location	Area Manager	Unit Manager	Site MgR	Worker
Digger	MCR1	AMI	Dave	Busy	Worker 9
Digger	MCR2	AMI	Joe		worker 10
Digger	MCR1	AMI	Dave	Busy	Worker 2
Digger	MCR1	AM2	Dave	break	Worker 3
JCB	MCR2	AM2	Joe		Worker 2
JCB	MCR2	AM2	Joe		Worker 2
JCB	MCR1	AM3	Dave	break	Worker 1
JCB	MCR2	AM3	Joe		Worker 2
Crane	MCR3	AM3	New		Worker 3

 

[sandy666]

You can try Power Pivot and DAX but I am not sure it will be possible also but maybe I'm wrong

 

[alansidman]

With Power Query, you can build a parameter that will allow you filter on the Unit Name. Here is a tutorial on how to filter in PQ using parameters.