How NOT to show 0% values in Pivot chart?

[Mirisage]

Hi Forum,

Using “Data” tab in the attached Excel file, I have created the Pivot 1 in “Pivot” tab.

Then I copied the entire pivot 1 and pasted it in a new place in the same sheet starting from column k (let’s call this “second pivot” for now).

Then I placed the cursor on the second pivot and right clicked, then clicked “show value as” and then clicked “% of row total”.

Likewise I created the “Pivot 2”.

Then I created Pivot Chart 1 and 2.

Q:
In “pivot chart 2”, I want to show the % income of village 1 only for the last 3 months (like in Pivot Chart 1).

Could you please let me know how to do it.
Thanks
Mirisa

[hlhans]

Hello Mirisa,

what do you think about the following: Change the chart type of Chart 2 to "100% stacked column" and you will easily cover your problem. In addition, this way expresses more obviously what you are after, isn't it?

Kind regards from Hamburg

Henning

[Mirisage]

Hi Henning,
Thanks.

The problem is my Manager wanted line charts which cannot be compromised.
So, I want a solution in terms of a line chart.

Could you or any other expert can help me.

Thanks
Mirisa

[hlhans]

Hi Mirisa,

sorry - I had a look at it again - I have no idea --> persuade your manager: stacked columns are super pretty and serve the purpose much better


Otherwise do not use pivot charts and make use ordinary charts instead. If you implement the formula na() as a replacement of those values = zero, the line will be interrupted

Best regards from Hamburg

Henning

[Mirisage]

Thanks Henning anyways.
Regards
Mirisa