pivot table filter field and use it as value

[ajm]

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.

 

[Alex Blakenburg]

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]

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]

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.
   

 

[ajm]

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.

View attachment 36185​

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.

Finally, here is the underlying data:

Party Type	Creditor Party ID	Collected
Organisation	3731	464961.81
Organisation	26	247838.15
Organisation	53412367	244058.3
Individual	96124250	20000
Organisation	3376	169775
Individual	69345429	14050
Organisation	62827118	121879.74
Organisation	79403218	107748.93
Organisation	85050656	103332.35
Organisation	86847480	83121.6
Organisation	69554368	82596.05
Organisation	55258776	74447.74
Individual	95675377	-1670
Individual	91486951	-1700
Organisation	86374667	-2970
Organisation	67104869	-3000
Individual	94636180	-3050
Organisation	70557905	-3070
Organisation	91285247	-3077
Organisation	95678603	61000

 

[ajm]

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.

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

Compared to the count off al creditors

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]

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]

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]

Can you see if this works for you.
Create a new measure for Collected_Positive (and the opposite sign for negative)

=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

Party Type Pivot Filter

 

[ajm]

Can you see if this works for you.
Create a new measure for Collected_Positive (and the opposite sign for negative)

=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.