Pivot Table Help - Gross Margin % & % of Sales

[dza7]

Hello,

I have a challenging pivot table issue that I hope I can get some help
on.

Here is the spreadsheet I'm working with:
https://www.yousendit.com/download/OHo0N3RSSU90d0UwTVE9PQ

I'm having problems with showing percentages in my pivot table such as
gross margin and operational profit. Here is the P&L pivot table as I
have it now, as you can see I can't get the "Gross Margin %" to output
a number. Both "Gross Margin %" and "Operational Profit %" are
calculated fields I've added to the pivot table since the raw data
only includes sales, cost of goods sold & expenses.

I believe that this has something to do with the detail that I want to
show in different parts of the P&L. With that in mind I've attempted
to add new fields to the data to help but still no success. I can add
as much as it is needed to the data to make this work (outside of
changing the first column and the PTD, QTD & YTD figures which are
output from our system) but I'm at a standstill.

Can someone help!?! I have to roll this out amongst different
divisions and it's going to be a killer tool if I can figure it out!

Thanks in advance.

Dza

 

[Roger Govier]

Hi

I fear that you cannot achieve what you want to do totally within the PT.
The way in which calculated fields work, is that the calculation is
carried out on each sub-group, and it is the accumulation of these
values that is shown as the % figures in each case.

Because Sales is Zero for lots of the categories, then that is why you
get the Divide0# error.
Even it you remove Description from the PT, and Exclude 0 from Brand,
you still get Divide0# errors as brand 3400, 5400 and 7400 have no sales
in the data set provided.
Inserting 0.01 value in the source data table for these brands,
eliminates the Divide0# error, but produces an incorrect result for the
reason outlined at the beginning.

What I would do, is remove both the % calculated items.
Insert 2 rows above your PT
In C1 enter Gross Margin % and in C2 Operational Profit%
The use the GetPivotData function with a formulae as follows

D1
=GETPIVOTDATA("Sum of PTD FRCST",$A$5,"Catergory","Gross Margin $")/
GETPIVOTDATA("Sum of PTD FRCST",$A$5,"Catergory","Sales")

D2
=GETPIVOTDATA("Sum of PTD FRCST",$A$5,"Catergory","Operational Profit $")/
GETPIVOTDATA("Sum of PTD FRCST",$A$5,"Catergory","Sales")

E1
=GETPIVOTDATA("Sum of PTD ACTUAL",$A$5,"Catergory","Gross Margin $")/
GETPIVOTDATA("Sum of PTD ACTUAL",$A$5,"Catergory","Sales")

E2
=GETPIVOTDATA("Sum of PTD ACTUAL",$A$5,"Catergory","Operational Profit $")/
GETPIVOTDATA("Sum of PTD ACTUAL",$A$5,"Catergory","Sales")

Format D1:E2 as Percentage

Not quite the layout you wanted, but with some nice formatting at least
all of the relevant data is provided to the users.

(By the way, I have left the spelling of Category incorrect, as you have
it, with an "r" in the middle)
If you need a copy of the file as i have amended it, then mail me direct at
roger at technology4u dot co dot uk
Change the at and dots and remove spaces to make a valid email address.