Pivot Table Help

Hi Guys,

Got a bit of an awkward problem and hoping I can explain it clearly, so here goes.

I have a whole lot of data (around 10,000 rows) with around 30 columns each (Not sure if i needed to tell you that, but put it in anyway).This data shows indivudal jobs we have done for customers. There's 3 columns I want to look at primarily : Customer, Revenue, Gross Profit.

I can happily add a column into this data with a formula to work out the GP Margin (%) for each individual job. I was wondering if there's any way, once the data is in a pivot table to work out the GP Margin of the customers total revenue and total gross profit? I can obviously add a formula to the side of the pivot table but obviously leaves manual work after the pivot has been refreshed.

Also along with this I want to segment the customers in to GP Margin groups (e.g, all those with 0-5% in group 1, 5-10% in group 2, etc) and then to be able to create a pivot pie chart showing the number of customers in each group. I've found a way to do this by creating a pivot chart of the pivot table but this obviously is a messy way to do it and doesn't work as soon as the data is updated or filtered.

I've tried to explain my self as clearly as possible, I hope you can work out what i'm trying to get at.

Thanks in advance for any help, this will help massively with my work

Hi NBVC, thanks for the reply.

Not sure where I would add these formulas? I've added a GP Margin column on to the main table to work out the % gross profit margin on that individual job, but then when it comes to the pivot and looking at each customer I have tried to do an average of all these figures to give me a gross profit margin of the customers total revenue and total profit (which will be around 100 jobs/rows in the main table) but this obviously gives a skewed picture and nor the result I'm looking for.

Of course,

What's the best way to upload/attach a file? I'm new around here :D

Have a look at calculated fields

Here: http://www.contextures.com/excel-piv...ted-field.html

Thanks for all this help btw,

I've attached a small sample with only a few rows.

On the data tab you can probably see i've added in the last few columns myself (the rest of the dat is grabbed from an external source). What i'm trying to do is create a pivot table as in the first tab showing the Shippers (Column E in the data) total gross margin percentage for the Shipper total (total gross profit/total revenue). I guess what i'm really looking for is the median of column X ? But i'm led to believe medians aren't possible within pivot tables?

After this (if it is possible) I'd then like to be able to allocate that shipper to a group based on it's gross margin..

Sorry if a bit confusing, thanks again .

Kyle123 at first glance that looks like just what I was looking for. Thanks very much.

Kyle thanks very much, that's helped me with part 1 of my problem. But I've tried to use this to create a calculated field with the formula "=IF(GPMargin<0,"Less than 0%","No") so i can segment the customer to a group. But it just returns #VALUE! when I use this. Is this because it can only perform calulations and not insert text? Is there any other way I could allocate the customer to a group?

And NBVC thanks for all your help as well.

Ignore me guys, I was obviously trying to be too precise I just ended up using numbers to group everything.

Thanks again for all the help