Pivot value filter based on Show Values As

[weeble33]

Hello,

I have a pivot showing Accounts and Account Types from a sales table. The two should be 1:1, but occasionally we see an account sometimes comes through with multiple types. I'm trying to use the pivot to show accounts with multiple types (without converting to power pivot). I've found that I can insert any field into the values section of the pivot and within the value field setting choose show values as % of parent total. Every account/account type combo shows 100% except the errors. Perfect, I've now identified the errors, but now I want to filter my list to only show those errors. In my drop down on the account, I want to go to value filters and filter only on the non-100%, except the pivot doesn't seem to recognize what's being shown, only the original count numbers.

See example below where Harry's hardware contains the multiple account types and thus I only want the pivot to show the account Harry's Hardware

Account	Account Type	Original Count	Show as % of Parent Total
Macy's	Retail	5	100.00%
Sbarro	Restaurant	8	100.00%
Harry's Hardware	Hardware	3	42.86%
Harry's Hardware	Retail	4	57.14%
Gamestop	Retail	9	100.00%

[Bernie Deitrick]

I know this is a workaround, but you could use a column of formulas, matching your pivot table length, with a formula like

=C5=1

and filter your sheet based on that result being FALSE.