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:

Name	Document No.	Values
John	11	$35
Mary	154	$558
Adrian	21	$120
John	14	$100
John	344	$48

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The first pivot is this:

Row Labels	Sum Values
Adrian	120
John	183
Mary	558
Grand Total	861

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Row Labels	Count of Doc Number
Adrian	1
John	3
Mary	1
Grand Total	5

<colgroup><col><col></colgroup><tbody>
</tbody>

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.