Is there a way to add a calculated field that would apply to total column only, without adding unnecessary detail for each column item?
Consider the following example.
The data is as follows:
Year Product Units Price Revenue 2013 Porduct A 100 1.5 150 2013 Porduct B 150 1.6 225 2013 Porduct C 200 1.7 300 2013 Porduct D 250 1.8 375 2013 Porduct E 300 1.9 450 2014 Porduct A 150 1.5 225 2014 Porduct B 200 1.6 300 2014 Porduct C 250 1.7 375 2014 Porduct D 300 1.8 450 2014 Porduct E 350 1.9 525
The resulting pivot is as follows:
Now, say, I want to add a 10% discount for each products total, however when I add a calculated field I get unnecessary discount details for each year separately:
What I really want is to get rid of the year-by-year detail and leave the calculation for total only:
But I'd like to achieve it without hiding the columns but simply adding a field, like I would ordinarily would do without pivot. The reason behind is that there might be a lot of consequent calculations that I'd like to perform without making pivot grow horizontally (especially when I do a month-by-month analysis it extends resulting table by 12 columns every time a calculated field is added.