Pivot table - how to show total at the end of the groupped month and add additional columns with caluclations?

[michaldybczak]

I realize that there is much to possible and much to learn about pivot tables. I am a bit better familiarized with Excel pivot tables and I’m a bit lost in LO.

1. How do I sum at the end of the group?

For example, in the date column, I grouped by years and months. I see total from all years and months below, but I also need totals per each month.

I know I could do calculations outside the pivot table, referencing to pivot cells, but professionally, all data should be contained in pivot table, which is the end result, while referencing to source data.

2. How to extend pivot table to have additional columns with calculations?

For example, my pivot table has more columns and I need to have additional column with calculations that are not present in the source data, because calculations need to be made on the cells of the pivot table.

Besides, when calculations are outside pivot table, the references are to cells directly, not to the data in pivot. This means that if I apply filters or additional grouping that will move data to different positions, all calculations outside table will break. That alone makes formulas outside pivot table an unprofessional and risky way of doing things.

Maybe some links to materials showing such operations would be helpful?
Thank you

[sokol92]

1. For subtotals, see for example here.
2. I’m afraid that calculated fields need to be added to the data source before building the pivot table. If the data source is a range of cells, you can add columns with corresponding formulas interactively or through a macro.

[michaldybczak]

Thanks, that solves the problem with subtotals, although it was at first hard to get to the proper option. The documentation was not clear enough that we must first enter the pivot table properties, then click on rows. Clicking on columns shows similar options but without subtotal, so only rows offer that possibility.

In my case, I was able to add the calculations to the source data, but that is not ideal. Source data will be added each month and that means, I have to do additional operations to fill additional columns that didn’t exist in the original, raw data. This is doable and not too much work, but still, this has to be done every month. If the calculations were done on the pivot level, all I had to do is to refresh the pivot table after adding new data. No tainting the source material. Simpler, quicker, and that is how it works in Excel. In Excel, I can also change names of the columns. In short, Excel is more powerful and flexible, and I hope that LO will get there some day.
Thank you