Add Pivot Table Column that Calculates Blank Cell Values

[Kenichi]

I'm having trouble adding a column to my pivot table that indicates what % of sub-items are not blank. For example:

I would like to get the value in D2 (10%). It's calculated by taking 1-90% (C3). This value indicates that "Sample Item" sold with another item 10% of the time.

Data explanation: 6% of customers that bought Sample Item also purchased Item A, and 4% of customers that purchased Sample Item purchased Item B. 90% of Sample Item sales sold without any other products, so I'm interested in noting that Sample Item had a 10% attachment rate with other products.

The main issue I'm running into is that my data in the Pivot Table is grouped, and needs to stay that way, so using a formula like GETPIVOTDATA doesn't work because it relies on the data being ungrouped.

Is there a way that I can add this % attachment column to my pivot table and have it calculate the % attachment for the parent items, ignoring all of the sub-items?

Thanks!

 

[sheetspread]

Did you try changing the blanks to "" ?

 

[Kenichi]

The blank values were part of a vlookup formula on the data source that returned "" if there was no match. I was able to get the blanks to disappear by copy/pasting as values and then manually hitting delete for all the blank cells. Not sure if there is a more automated way to remove the blanks from the pivot table, but that solution is working OK for me and I'm able to calculate the % attach now.

 

[sheetspread]

You can change the formula to return "no match" for filtering or an error like 1/0 that F5(Goto)-Special can highlight and delete.