Pivot table - filtering a field in source table isn't giving expected results in pivot tbl

**sharbie** · 06-30-2019, 06:21 AM

I have created a table and pivot table - see attached file. I'm using windows, excel 2016.

When I have the entire table selected (unfiltered), the pivot table provides what I expect, i.e. a breakdown of the table by 'Type', counting the number of each 'Type'. However, when I filter the table by "helper patient number" (column 1) by those starting with PC or PN, I want the pivot table to summarise the filtered information, i.e. for patient numbers beginning with PN, I want to see:

row
label Count
2 1
3 2
6 1
16 1

Instead I get quite different results as you'll see. I've obviously misinterpreted how pivot tables work. How do I create the pivot table to provide me what I want please? Or, is there a better solution? Thank you.

**Roel Jongman** · 06-30-2019, 08:15 AM

You need to filter in the pivottable and not in the datatable.
Filters on data table are not carried over to the pivot

**Roel Jongman** · 06-30-2019, 08:22 AM

See attchment with my reply..

**sharbie** · 06-30-2019, 08:37 AM

Thanks Roel, actually I can't see any changes in the table you sent back to me. But thanks to your explanation, I added a second "row" field to the first column in the pivot table, namely "helper patient number', set below the 'type' row and if I sort by 'helper patient number' (PC patients only) in the first column of the pivot table it works perfectly. Thanks heaps!

**Roel Jongman** · 07-01-2019, 05:58 AM

Great that it worked. I see I re-uploaded your original file instead of my version with changes..

so better late then never the right version..

**sharbie** · 07-01-2019, 08:01 AM

Gotcha. Thanks - I'm glad you sent your solution through. Learnt something new!

Pivot table - filtering a field in source table isn't giving expected results in pivot tbl

LinkBack

Thread Tools

Rate This Thread

Display

Pivot table - filtering a field in source table isn't giving expected results in pivot tbl

Re: Pivot table - filtering a field in source table isn't giving expected results in pivot

Re: Pivot table - filtering a field in source table isn't giving expected results in pivot

Re: Pivot table - filtering a field in source table isn't giving expected results in pivot

Re: Pivot table - filtering a field in source table isn't giving expected results in pivot

Thread Information

Users Browsing this Thread

Similar Threads

Pivot Table - Duplicated Values Giving Inaccurate Results

Pivot table giving wrong results?

[SOLVED] Pivot Table formula giving incorrect results

Adding a calculated field to a pivot table is not giving me the results that I think it sh

Pivot Table VBA - Data Field - Original (source) field name

[SOLVED] Field Source Error for Pivot Table

Pivot Table - Filtering Page Field

Bookmarks

Bookmarks

Posting Permissions