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
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
629
Office Version
  1. 365
Platform
  1. Windows
What name did you give the measure and did you use that measure in your pivot table ?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
Hi Alex, for reference, i have three columns in my table:
Party TypeCreditor Party IDCollected

my measures as they appear in the Manage Measure pop up:

1618536374655.png


and the pivot table filter:

1618536554087.png
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
hang on a minute. I think the train has just got to the station, Alex. Use Collected_Positive in place of Sum of Collected in the Values pane of the PT Wizard.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
Using Collected_Positive in the values pane of the PT wizard now gives me the correct total. UniqueCount and DistinctPartyID will only go into the Values pane and then give me the total number of records.

1618538985808.png


with Party ID dragged into the Rows, it looks like this:

1618539253220.png


Individual count should be 26,226 and Org 32,035.
 

Alex Blakenburg

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

ADVERTISEMENT

Individual count should be 26,226 and Org 32,035.

How are you working out your check totals of 26,226 & 32,035 ?
Did you apply the filter on Party Type to only pick up Collected_Positive > 0 ?
(this is assuming you don't want to count any customers that only have negatives)
Do you have any way of working out what is getting picked up in the pivot that is not in those numbers ?
And then work out what is different about them.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
629
Office Version
  1. 365
Platform
  1. Windows
Actually this looks like it might work.
I added another measure called DistinctPartyID_Positive and included it in my pivot.
It seems to work on my limited data set.
You can ignore my comments regarding a filter since that only filters at the total level (an issue you encountered early on in the piece).

The code for the measure is this.

CALCULATE(
DISTINCTCOUNT( tbl_Creditor[Creditor Party ID] )
,tbl_Creditor[Collected] > 0
)
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
Actually this looks like it might work.
I added another measure called DistinctPartyID_Positive and included it in my pivot.
It seems to work on my limited data set.
You can ignore my comments regarding a filter since that only filters at the total level (an issue you encountered early on in the piece).

The code for the measure is this.

CALCULATE(
DISTINCTCOUNT( tbl_Creditor[Creditor Party ID] )
,tbl_Creditor[Collected] > 0
)
can you believe i got it to work, left the workbook open, then when doing something else the next day, closed all books without saving!! what an idiot. I used filter in the formula somehow. will have another crack at it tonight and will try your formula above also.
 

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