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]

What name did you give the measure and did you use that measure in your pivot table ?

 

[ajm]

Hi Alex, for reference, i have three columns in my table:

Party Type

Creditor Party ID

Collected

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

and the pivot table filter:

 

[ajm]

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]

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.

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

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

 

[Alex Blakenburg]

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]

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]

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.

 

[Alex Blakenburg]

Sounds promising.