Adding calculated columns to Excel Pivot without details

Question

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.

Answer 1

Out of the box standard Pivot Tables don't allow this.

First I'll describe how you could work around this using regular Pivot Tables, secondly I'll show you how you can achieve this using the PowerPivot add-in.

---

Option 1: Two Standard Pivot Tables

I'd tackle it by simply sitting two pivot tables next to each other, one with years as a column label, and one without (this second table sitting one row lower so they line up).

You would need to make sure the row labels columns are sorted and filtered identically. Then you can just hide the labels column on the second table.

(In this screenshot I've manually added "Total" labels in row 3)

---

Option 2: Using PowerPivot

Using PowerPivot and DAX you could create all these columns as individual measures so you can control the filters applied quite precisely.

First make sure you have PowerPivot installed - Excel doesn't ship with it apart from in certain editions of Excel 2013. I won't walk through the install process but a quick search should help if you're stuck.

Select your data range and click Create Linked Table on the PowerPivot toolbar.

The green PowerPivot window should appear. By default your table will get simply named Table1 - let's rename it ProductSales to make it more meaningful.

Click the PivotTable button in the middle of green ribbon and place it wherever you like. Now we need to create some measures. PowerPivot uses a type of code called DAX (Data Analysis eXpressions) which is a lot like normal Excel formulae.

Back in Excel, start by adding Product to the Row Labels of your PivotTable. Now click the New Measure button on the PowerPivot ribbon tab.

In the resulting window, set Measure name (all PivotTables) to Units Total and in the Description free text box enter:

=sum(ProductSales[Units])

Create another measure, called Units 2013. Enter this code:

=CALCULATE([Units Total],ProductSales[Year]=2013)

The CALCULATE() function in DAX allows you to apply (multiple) filters to an aggregated expression - so here we're filtering the Units Total measure we've just created, by the relevant year. Repeat for Units 2014, and then do the same again for Revenue Total,Revenue 2013 & Revenue 2014.

You can now order and format your columns as you like - to mimic your screenshots above I've manually set the 2013 & 2014 columns to grey.