PIVOT TABLE and CHART hiding blank cell HELP

Copper Contributor

I'm having trouble with my pivot table & chart. 

Basically, I have tons of housing data where each entry (Each house) has an assigned county, high school, price, and status (either "Sold" or "Active").

 

Here's a what my Pivot Table looks like when I break out the data by high school, status, and price range:

Capture1.JPG

 

What I'm interested in is how many more properties have a "Sold" status than an "Active" status. I can do this by going to the 'Value Field Setting' and changing 'Show Value As' to 'Difference From' ... 'Base Field' = 'Property Status' ... 'Base Item' = 'Active' which gives me this table.

Capture2.JPG

 

THIS is the data I need but I want to hide the now blank 'Active' rows. When I try to put this in a Pivot Chart it looks ridiculous because half the chart is showing the blank 'Active' info. 

 

Capture3.JPG

 

Is there a fix for this? When I filter the data to only show the 'Sold', I get a #N/A error in all of my pivot table cells and my chart goes blank.  

Capture4.JPG

1 Reply

@PiedmontRes 

 

It's hard to help without a sample of the original spreadsheet itself, so if it's possible (especially if the suggestions you receive don't work), could you upload such a sample?

 

In the meantime, one thought that occurs to me is to work with your raw data first, to create a "helper column" that does that calculation on the comparison of the numbers sold vs the numbers active, putting the result into the "Sold"

 

Or maybe it's a matter of using subtotals by town or county or zipcode--whatever that horizontal axis is--

 

In any event, I'd try to tackle it at the level of the data itself before creating the Pivot Table.