pivot table filter field and use it as value

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
i believe its possible to filter a field before dragging it to the filter, column, row, or value pane when creating a pivot table using the pivot table wizard. Just can't figure it out.

in the image below, you can see that i have already used Collected in the value field. The pivot table sums amounts per Creditor. I am only wanting to show creditors with a value owing greater than 0. if i drag Collected to the Filter pane, i don't get the same options to filter the data. once its in that position, i can manually deselect Collected amounts less that 0 but that takes forever.

1617862930695.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
629
Office Version
  1. 365
Platform
  1. Windows
Its a little unclear on where you are looking and the image is a bit small to read the words but I think what you want is:
  • goto to the pivot table
  • I think your row field is called Party Type
  • Click on the drop down on Party Type
  • Select Value Filters
  • Select greater than and enter 0
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
Hi Alex, that's what i was doing. can you think of any reason that it wouldn't work? the values are properly formatted as numbers in the source data. can't think of anything else at this stage of the evening.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
629
Office Version
  1. 365
Platform
  1. Windows
  1. Can you change the number formatting in the pivot show you know for sure its being recognised as numbers
  2. Can you give me a better screenshot something line the below image.
    After you hit the drop down click on <windows> + S to take the Snip
  3. Another picture showing at least the headings and a couple of rows
  4. Ideally some of the underlying data using XL2BB so I can try the pivot from my end.
1617873418562.png
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808

ADVERTISEMENT

  1. Can you change the number formatting in the pivot show you know for sure its being recognised as numbers
  2. Can you give me a better screenshot something line the below image.
    After you hit the drop down click on <windows> + S to take the Snip
  3. Another picture showing at least the headings and a couple of rows
  4. Ideally some of the underlying data using XL2BB so I can try the pivot from my end.
Thanks Alex. Definitely numbers. the raw data adds up. thats my usual test. then when i am feeling paranoid, I multiply all by 1.

this is the pivot before attempting to filter the Sum of Collected amounts for those greater than zero. Count of Creditor Party ID is a Distinct Count. To do this, when setting up the pivot table, check the Add this to the Data Model box on the Create Pivot Table wizard.
1617922410110.png

1617922658423.png


Finally, here is the underlying data:
Party TypeCreditor Party IDCollected
Organisation3731464961.81
Organisation26247838.15
Organisation53412367244058.3
Individual9612425020000
Organisation3376169775
Individual6934542914050
Organisation62827118121879.74
Organisation79403218107748.93
Organisation85050656103332.35
Organisation8684748083121.6
Organisation6955436882596.05
Organisation5525877674447.74
Individual95675377-1670
Individual91486951-1700
Organisation86374667-2970
Organisation67104869-3000
Individual94636180-3050
Organisation70557905-3070
Organisation91285247-3077
Organisation9567860361000
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
I think the issue arises because the filter applied in the Pivottable Field List before dragging the field into the filter pane is attempting to filter the Sum of Collected amounts as opposed to filtering on the individual Collected amounts as seen in the table.

1617925501644.png


I want my end result to be a Count of Creditor Party Id which has excluded the negative collection amounts

1617925892553.png


Compared to the count off al creditors
1617925957498.png


The files are locked down in an organisation that actively discourages change. "if its not broken...." Adding a helper column is about the only thing i can think of doing to achieve what i need. Any thoughts?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
629
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I have been looking at it but need to go out for the day.
I arrived at the same place you are at. If you create a unique record that is negative it drops off.
I suspect you can do something in measures.
If you don't get anyone else to buy into it later today. I will have a look tonight (I am in Sydney Aust and its 10:20am here now)
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
I have been looking at it but need to go out for the day.
I arrived at the same place you are at. If you create a unique record that is negative it drops off.
I suspect you can do something in measures.
If you don't get anyone else to buy into it later today. I will have a look tonight (I am in Sydney Aust and its 10:20am here now)
Thanks Alex. In Brisbane.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
629
Office Version
  1. 365
Platform
  1. Windows
Can you see if this works for you.
Create a new measure for Collected_Positive (and the opposite sign for negative)
Excel Formula:
=CALCULATE(sum(tbl_Creditor[Collected]),tbl_Creditor[Collected]>0)

Then filter Party ID on Collected_Positive > 0, to make sure your count only includes customers that have at least 1 positive line.

Manage Measures Screen
1617968624142.png


Party Type Pivot Filter

1617968848986.png
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
Can you see if this works for you.
Create a new measure for Collected_Positive (and the opposite sign for negative)
Excel Formula:
=CALCULATE(sum(tbl_Creditor[Collected]),tbl_Creditor[Collected]>0)

Then filter Party ID on Collected_Positive > 0, to make sure your count only includes customers that have at least 1 positive line.

Manage Measures Screen
View attachment 36310

Party Type Pivot Filter

View attachment 36312
HI alex, I have added the measures but don't seem to get anything working differently to what i had already. I will have to do some reading up on power pivot tonight to see if i am doing anything wrong (well, obviously, doing heaps wrong otherwise it would be working, right!!).

all i did was go to the power pivot menu, add measures, then copied what you had in your suggestion above for formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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