1

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:

Resulting pivot

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:

Pivot with Calculated fields

What I really want is to get rid of the year-by-year detail and leave the calculation for total only:

Desired output

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.

  • Could you add a custom calculated field that is the formula that sums the discount? Say, doing the calculation within that field and showing the result? Or do you need the results of the steps available to see, but want to hide them normally? – Raystafarian Sep 9 '14 at 12:25
  • @Raystafarian, I need the results of the steps, but only for Totals. Discount field is currently a custom calculated field. While it solves the issue, it also adds unnecessary columns. – turezky Sep 9 '14 at 12:57
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.

enter image description here

(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.

enter image description here

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.

enter image description here

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])

enter image description here

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

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

enter image description here

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.

enter image description here

  • Unfortunately hiding will not solve the issue, since it's basically the same workaround I'm currently using (and without a risk that some rows/columns will mismatch. On the other hand, PowerPivot might not be an overkill after all. If it can solve the issue, it is OK. I guess the problem with PowerPivot, is that it should be installed on other people's computers if they will want to manipulate the table. – turezky Sep 9 '14 at 12:58
  • So, how do you do it with PowerPivot? I've just added a measure, but it yields same result. – turezky Sep 9 '14 at 17:52
  • I've expanded my answer to show a PowerPivot solution. – Andi Mohr Sep 10 '14 at 10:25
  • So it basically means manually creating an entry for each year. It is the shortcut solution for the given case, but it might become a pickle when columns are months or when they are a longer list (names, regions, etc) that should be generated automatically. – turezky Sep 10 '14 at 17:03
  • Yep, both have weaknesses. If you've got many columns you need to do this for, I'd suggest the first option is the best one. With a bit of VBA you could enforce common sorting on both tables. – Andi Mohr Sep 11 '14 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.