Changing a pivot's displayed values (from one set to another) using a slicer

[Alice Morland]

Hello forum!

I have a question for you knowlegeable people; how can I change what's in a pivot's values without actually going to the PivotTable Fields area. Ideally, I'll be using a slicer.

This is the raw data:

[TABLE="width: 255"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Document No.[/TD]
[TD]Values[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]$35[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]$558[/TD]
[/TR]
[TR]
[TD]Adrian[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]$120[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]344[/TD]
[TD="align: right"]$48[/TD]
[/TR]
</tbody>[/TABLE]

The first pivot is this:

[TABLE="width: 195"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum Values [/TD]
[/TR]
[TR]
[TD]Adrian[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]183[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]558[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]861[/TD]
[/TR]
</tbody>[/TABLE]


I'd like to replace the Values column by the Count of Document Numbers:

[TABLE="width: 277"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Count of Doc Number[/TD]
[/TR]
[TR]
[TD]Adrian[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


In conclusion, is there any way to do that without going into the PivotTable Fields area? I'm asking because the report users would rather not get technical.

Big thanks!!

 

[SpillerBD]

Create BOTH. Then Record a macro to Hide each column or un-hide all columns.
Use a button control to invoke each desired macro.

That should be three separate macros based on your descriptions.

You can use the same method to actually modify the PivotTable, but that will be more complicated code.

 

[Alice Morland]

Hi Spiller, thank you. I'll give it a try with macros. The thing is, I did see some complicated formula somewhere using DAX.

 

[SpillerBD]

DAX !
I still wishing that would click in my head, but I think you can do it with DAX (PowerPivot or PowerBI Desktop)
Something along the parameters line.
Check out the P3 blog (https://powerpivotpro.com/) and the PowerBI channel here.
I just don't enough experience/knowledge to give you any more advice than that.

 

[Alice Morland]

Thanks for taking the time though I'll look for a solution and I'll post it here - who knows, it may be useful to others too.