Excel Pivot Table difference in columns of "shown as" values

Question

Is there a way to let a pivot table calculate the difference between 2 columns automatically when the values are shown as a % of the parent column total?

Now I need to to manually but the table is dynamic and number of competitors may vary. Function seems so easy but can't find it after googling etc...

See example picture below of what I want to achieve.
(Column F automated by the pivot table is the goal)

Solution

If trying to solve this with PivotTables, you've got a couple of options:

1. Use a 'Traditional' PivotTable that's based on a range. This will give you percentage differences, but you can't get percentage point differences like you're asking for without using external formulas.
2. Use a 'OLAP' PivotTable that's based on data you've added to the Excel Data Model. This will give you both percentage differences and percentage point differences, without having to resort to using external formulas.

In both cases, I recommend that you unpivot your data first, so that it is in what's known as a Flat File. Currently you're using a cross-tabulated data source (i.e. your source has columns called Year 1, Year 2), and the type of percentage comparisons across years you want to do doesn't work if your data is a crosstab. Basically, PivotTables aren't meant to consume cross-tabulated data.

Instead, you really want your data laid out so that you have a column called Amount and a column called Year, and then you can use the Show Values As options available from the right-click menu to show as percentage differences across years. To transform your data into a flat file, see my answers at convert cross table to list to make pivot table

That said, you can still use the GETPIVOTDATA function on your existing (unpivoted) data layout in a way that is somewhat more robust to changes in your PivotTable structure than just subtracting one reference from the other:

But again, I recommend transforming your data into a Flat File. Then you can additionally do the following:

Using a 'Traditional' PivotTable:

You can kinda solve your problem entirely within a self contained 'Traditional' PivotTable if you drag the Amount column to the Values area, put the Year column in the Columns area, put your Competitors in the Rows area, and choose one of the percentage Show Values As options you'll see when you right-click a cell in the Values area.

I say kinda, because without using external formulas (or without calculating the percentages back in your source data), you can only get it to show percent increases (see far right column), not percentage point increase like you want (see far left column). That said, I think percent increase is less confusing. But I guess it depends on what you want to show. If you want to show say change in market share from one year to the next, then percentage points make sense.

Of course, you could always use the GETPIVOTDATA function to do the additional math for you like we did earlier, like I've done on that left hand side.

Using an OLAP PivotTable based on the DataModel

Calculating percentage point increases likely requires using PivotTables built using the Data Model. See my answer at https://stackoverflow.com/a/49973465/2507160 that explains a little about the Data Model (although it doesn't answer this specific question).

Here's the result:

Here's the measures I used to do this:

Total Year 1: =CALCULATE(SUM(Table2[Value]),ALLSELECTED(Table2[Competitor]),Table2[Year] = "Year 1")

Total Year 2: =CALCULATE(SUM(Table2[Value]),ALLSELECTED(Table2[Competitor]),Table2[Year] = "Year 2")

% Year 1: =CALCULATE(SUM(Table2[Value]),Table2[Year] = "Year 1")/[Total Year 1]

% Year 2: =CALCULATE(SUM(Table2[Value]),Table2[Year] = "Year 2")/[Total Year 2]

p.p. Diff: = [% Year 2] -[% Year 1]

Comments

[jeffreyweir]

What version of Excel do you have? 2013 or later?

[jeffreyweir]

In addition to my question above, in regarding to wanting to calculate the percentage point change...are you wanting to show how the market share of competitors changes year on year?

[jeffreyweir]

Are you there, Jelle?

[Jelle De Herdt]

Thanks for your feedback. I need this in the structure as this will be applied for different countries with a different number of competitors. Therefore, the proposed solution does not seem to work.

[ashleedawg]

I don't think you understand. That was just an example of how to add a calculated column, which is what you want to do.

[ashleedawg]

So @jeffrey, we meet at Pivot Tables again... Perhaps I'm the one misunderstanding this time... but Show values as... has options for % Difference From... (which as you pointed out, would not be suitable) but also for Difference From... which ought to work just fine...? ie., right click a field > Show values as > More options

[ashleedawg]

@jerffrey - see the images I added to the bottom of my answer

[jeffreyweir]

@ashleedawg That website is run by Debra Dalgleish, a Microsoft Excel MVP, and is the best PivotTable resource on the web. (Deb has written two great books on PivotTables...see contextures.com/xlbooks.html). I don't think a Calculated Field in a non-OLAP PivotTable is going to solve this, because while you can do a subtraction it is on the underlying fields, which aren't in percentages to start with. You could do this with DAX, but I'm still waiting to hear back from the OP whether they have Excel 2013 or later (meaning they have a version that has the DataModel built in).

[jeffreyweir]

@ashleedawg The Difference From option gives the absolute difference between the raw data, which is in euros, not percentages. The OP would have to calculate percentages in the data source in order to use that option. Which is doable. But if they have Excel 2013 or later then I'd suggest a better option is to do it using DAX. I'll see if I can post an example in the next few hours.