Hi,
I have a pivot table and chart attached. I do not want the zero values to be shown on any charts. can anyone help?
Hi,
I have a pivot table and chart attached. I do not want the zero values to be shown on any charts. can anyone help?
a simple solution would be to modify your calcs on row 56 on data tab, and replace the 0 (in the IF) with either NA() or 1/0
if you repeat for each column, and refresh the Pivot you should find those data points are subsequently ignored in the Chart.
Hi, sorry, I replied already but not sure if it got through. thanks for your solution - I copied both the NA() and 1/0 across all formulas but when I refreshed the pivot all the data was showing either NA or DIV/0 so no data on chart at all. I may have done something wrong. Any chance of making the change to my s/s and sending it through? thanks so much for your help.
refer attached - only change being modification of row 56 calcs (which propagate given Table), and refresh of Pivot.
I am using O365 so, if this doesn't render correctly for you then this implies a difference in behaviour between the versions.
thanks for sharing. The solution somehow doesnt work for 'all' programs and projects - just gives NA for everything. Strange. Thanks for responding to me. Appreciate the support. Best, Shane
Shane,
I see what you're saying - yes, where you end up with #N/As across the categories for given intersection the resulting Pivot won't display correctly.
I am not sure there's an elegant way around this -- you might consider something ugly like the below ?
(to test, right click on the Pivot Graphs tab, select Code and paste below into resulting window)
This basically just:
1. copies the Pivot Range (bar Page Fields) to AA1, whenever the Pivot is updated
2. replaces the 0s in that range with #N/As before
3. re-setting the source range of a new standard Chart (named Chart ALT)
obviously, you'd need to setup axis limits / labels / headers etc per preference
Of course, the major downside is that the new Chart may not prove quite as flexible as the Pivot equivalent - it would pick up data field additions / removals etc by default.
Please Login or Register to view this content.
In Q56
=IF(Q2="",#N/A,+P56+Q2)
Instead of 0, drag across and down, then refresh your pivot/graph.
Line graphs will not pick them up but if you use your raw data elsewhere that requires calcs then this isn't a good idea.
If you like my answer please *Add Reputation
thanks for the reply - much appreciated !
thank you for the additional reply XLent and the extra details you have posted. Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks