Use Multiple Filters Within a Pivot Table

Greetings all,

I have a pivot table in this kind of format:

------------------
Column Labels
-phase1 -phase2 -phase3
Row Labels initial copy trial test trial final test
-Person A 26 27 14
Item1 1 1 0
Item2 0 1 1
Item3 1 0 1
Item4 1 1 1
-Person B
.....

------------------

I want to apply a filter that fulfills multiple criteria:
(1) phase1
(2) value = 0

This would transform my pivot table up above by removing item2 (removing the entire row) for Person A, but all other rows would remain visible.

Stated differently, I want to exclude any rows when the value for phase1 = 0 (do not even plot them).

I can do this by copying the pivot table to a new sheet and using a manual filter for that column, but how might one do this within the actual pivot table?

Thank you!

Attach a sample workbook (not a picture or text in the post). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hello,

I have attached the file.

BEFORE tab: standard pivot table
AFTER tab: desired pivot table -- do not plot rows where phase1 value = 0 (ignore those trials)

Please note that it is not as simple as excluding specific "cues" in terms of text values, as I have a very large dataset where the trials will change from user to user.

Thanks for any assistance.

Can you explain more about what you changed? I see the new sheet, but to me it just looks like you manually deleted the 0s from those rows. I am looking for a way to remove them entirely from the pivot table.

I also am unfamiliar with PowerQuery and PivotTable.

Any further advice is appreciated. Thank you.

if you've PowerQuery installed I can explain but if not - it doesn't make sense

you'll need get and install PowerQuery add-in for Ex2010 Pro Plus (PowerPivot also )

Originally Posted by sandy666

if you've PowerQuery installed I can explain but if not - it doesn't make sense

you'll need get and install PowerQuery add-in for Ex2010 Pro Plus (PowerPivot also )

Thank you for the clarification. Are those free add-ons? Where can one get them?

Also, is there a way to just remove those rows completely with these add-ons? They are currently still being plotted but with empty cells. Thanks so much.

Originally Posted by sandy666

PowerQuery and PowerPivot are free from MS site (you need to find it)

are you talkin' about these columns in PT to remove blanks?

copyPicture consonantsOnly standardTest vowelsOnly FinalTest

Awesome, thanks.

If you look at "AFTER" sheet, I have highlighted 2 rows in yellow. I highlighted them because phase1 values = 0 for those rows. Your solution has those rows in the table, but instead of 0s there are empty cells.

I want the final product to REMOVE those rows entirely (so that the only rows plotted have values of 1 for phase1).

like this one?

Yes, that's exactly right.

so....

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.