Create a “dynamic” calculated item in pivot table

1
votes

I have a set of data that looks like below:

Business Unit  Year  Customer   Sales$   GP$
Beverage       2011  ABC1       500      60
Beverage       2012  ABC1       500      60
Beverage       2013  ABC1       500      60
Beverage       2014  ABC1       500      60
Beverage       2015  ABC1       500      60
Property       2011  ABC1       500      75
Property       2012  ABC1       500      75
Property       2013  ABC1       500      75
Property       2014  ABC1       500      75
Property       2015  ABC1       500      75
Retail         2011  ABC1       500      60
Retail         2012  ABC1       500      60
Retail         2013  ABC1       500      60
Retail         2014  ABC1       500      60
Retail         2015  ABC1       500      60

What I'm trying to do is to create a by Business Unit by Year pivot table with sales, GP and GP% (GP% is a calculated field) on the row label, the desired format will be like this (the columns highlighted in yellow is the format I want to add to the pivot table):

Pivot table with year subtotal

I can get it done by adding a calculated item on the column Business Unit to sum the sales, GP for all 3 business units in respective year, and the calculated field for GP% can be shown correctly, but the issue is that when I only choose to see any 2 of the business units, the calculated item on the column still show the results of the 3 business units because the formula I type is 

=Business Unit[Beverage]+Business Unit[Property]+Business Unit[Retail]

so instead I tried to use relative reference in the formula by

=if(iserror(Business Unit[-3]), Business Unit[-2]+Business Unit[-1],Business Unit[-3]+ Business Unit[-2]+Business Unit[-1])

the if formula indeed worked but the fact is I have 20+ business units in my real data, and the number of characters I input in the formula box will exceed 255.

I'm now clueless on how to show the correct subtotal column for the year in the pivot table. I searched in the internet and one of the workaround to this is add some columns and formula next to the pivot table but its too clumsy as I have lots of formatting in the pivot table and slicers. Can it be done via powerpivot, if yes....can you be kind enough to specify the steps as I have no idea on how to use powerpivot. Any suggestions are highly appreciated! Thanks!

1
excelpivot-table
There is currently a powerpivot course running on EdX (courses.edx.org/courses/course-v1:Microsoft+DAT206x+1T2016/…). I think what you want to do is a fairly basic use case of measures in the data model which is covered pretty early on in the course. That is if I've understood correctly what you are asking. – Dan

1 Answers

0
votes

It's not exactly what you are after, but I managed to get the columns to add up in the way you require (the totals change if you remove a business unit). They are just shown after each year rather than at the end... Further tinkering may create your desired format

screen http://s9.postimg.org/boi0zgj1a/Untitled.jpg screen2