Dec 13 2019 11:29 AM
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:
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.
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.
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.
Dec 13 2019 11:54 AM - edited Dec 13 2019 12:10 PM
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.