Prevent pivot line chart dropping

[sharshra]

I'm facing a problem with pivot chart dropping when the value in table is empty or zero. After searching internet, I see that it is a common problem faced by many, but couldn't find a proper solution. Searched in mrexcel forum also, but couldn't find a solution. One of the solutions suggested was to use a field to have NA() & built chart on that. Is there a better solution which can be done without having to insert a new field? Is there any setting that can achieve this?

Table below is a much simplified version of the real table, but it shows the issue. Can any experts in this forum help?

Row Labels	Sum of Number
Aug-23	100
Sep-23	50
Oct-23	0
Nov-23	25
Dec-23	0
Jan-24	33
Feb-24	77
Mar-24	68
Apr-24	97
Grand Total	450

Pivot chart with zeros dropping.

Expected chart

 

[awoohaw]

I don't think you need to create a new field/column. Or maybe I don't understand your question completely. In your example, if you have values that are Zero and you do not want the chart to drop to zero, put a Conditional IF statement in

=IF(calculation or value = 0, NA(), calculation or value)

subsequent edit: I realize now you're asking about data from Pivot Tables. You could create a calculated crosstab with formulas. And, as you say add a calculated field in the PIVOT that replaces the field currently being charted.

 

[sharshra]

Sorry, the simplified table didn't created properly. In the actual pivot table, the cell is blank but the pivot line chart drops to zero when the cell value is empty.

Is it possible to achieve this without having to add a additional / calculated fields? Is there any setting available to achieve this?

 

[awoohaw]

I can only do it iwth the calculated field in the PIVOT TABLE ANALYZE part of the ribbon:
(I also could not use the Data Model to create the calculated field. When I create the PIVOT TABLE, I needed to uncheck the "Add to Data Model" box.)

 

[sharshra]

I think I figured out what is happening. When there is 0 in the source table from a formula, the pivot table displays zero & accordingly pivot chart also drops. If I remove the formula, pivot table displays blank & pivot chart also doesn't drop to 0.

I understand there are workarounds to replace 0 with N/A in the source table. But I'm wondering if there is no better way to handle this excel instead of using a formula just to get this basic thing done. I will be surprised if we have to use workaround to fix this very commonly occurring scenario.

 

[awoohaw]

Well zero is a value, so if the chart engine sees a value it will try to map it. That is why NA() and probably blank cells, too don't map anything

 

[sharshra]

I understand that 0 in pivot table will result in line chart dropping to 0 in pivot chart. But is there a way not to push 0 in pivot table without having to use formula to replace 0 with NA?

To complicate the matter, I found that one of the column in source table has a formula resulting in 0 value but pivot table displays blank & pivot chart is not dropping to 0. But other columns are displaying 0 in pivot table & pivot chart dropping to 0.

I can't share the table due to confidentiality clauses & also couldn't recreate similar situation in other sample tables. Curious to know if anyone have come across such situation?

 

[sharshra]

One more observation. I used formula to replace 0 with N/A in the source table. With this, pivot table had N/A & pivot chart didn't drop to 0. Looks good

Than, I removed the if condition in the cell to replace 0 with N/A. With this, cells in the source table have the original formula, some of which results in 0 value. Now, in the pivot table I can see blank instead of 0 which is good, but in the pivot chart, line is dropping to 0 which is not good Not sure what is happening Does anyone has any clue?

 

[sharshra]

I found a solution some time back. Thought of sharing it in this forum which may be useful for others. It's quite simple!! Sometimes, tough situations can be answered by a known solution which we would have forgotten or ignored!!!

Solution:
In the source table, I just changed the formula using 'IF'. If a condition is true, do something, else have a space. For example, see the dummy formula below.

IF(A1>0,A1,"")