Calculated Items displays "0" instead of no value

[luiz.morello]

Hello guys,

I'm having a problem with my pivot table using calculated items.
I have a table that controls the accounts payable from an organization, item by item, classified by type as "Financial", "Operational" and "Taxes". Over this table I apply a pivot table so I could get the total value by Month and Year (So, month and year are the line fields and Type is the columns field).
It happens that I would like to apply a line pivot chart over this pivot table, but I would like to show the grand totals for each month in the graph too. We all know that pivot charts doesn't display totals, so my fix was to create a calculated Item, summing Financial + Operational + Taxes, but when I do so, my pivot tables show "0" in the totals for month 1, 2, 3, 4 (that has no value in financial, operational and taxes). I have tried to apply a value filter "not equal to 0" in the column, but it still displays the "0", breaking all my graph.
Any thoughts on how could I hide those values so the table and the graph doesn't show 0 values? If it helps I could attach my sheet.

I hope I`ve made myself clear, sorry for the poor english!

[romperstomper]

Unfortunately calculated items do that. If you can post the workbook, we may be able to think up a workaround.

[luiz.morello]

Yes,

I wonder why do the dev's does not include an option "show grand totals in graphs". It would be very usefull to analyse acumulated values of categories or types. Lame :/

Anyways, here goes the workbook.

Cheers.

[romperstomper]

No workbook?

Generally, grand totals would distort the graph axes so I don't think they would be useful.

[luiz.morello]

Sorry. Here goes.

[romperstomper]

Have you considered using a stacked line chart instead, so the top line effectively shows the total?

[luiz.morello]

I had, problem is, I need to distinguish what is Tax payment, what is Financial payment, what is operational payment and the totals. If I use a stacked line chart, I get only 3 series, the top line assumes the Tax series and distorts the analysis.

[romperstomper]

It still does distinguish, you just have to look at the gaps between the lines.

Beyond that, I think you'd have to either skip the pivot and use formulas, or use named ranges and a regular chart built off the pivot table range.

Attached is an example of the named range approach.

[luiz.morello]

Thx for the replies romperstomper,

I managed to hide the 0 values applyin' a row filter... I was actually trying to apply the filter on the columns, that is why it didn't work.
Anyways, thx for the help!