Pivot Table - Sum column

[OceanIT]

Hi all

Struggling with something that is probably really easy to do, but can't get my head around it.

Basically I've downloaded from our Accounts system all products sold to customers and the sales price.

Pivot table shows these products, the QTY sold (sum) and the Sales Price (sum) but doesn't show the customer column.

What I'm now trying to do is add another column to the end of the Pivot Table so I can get the Sales Price (sum) for just a specific customer to compare to the overall Sales Price (sum)

I've managed to use a SUMIF in the data table which totals the Sales Price where the criteria is product code and the specific customer.
This value then appears in every row where that product has sold to the customer - and when this column is added to the Pivot table, it sums them all together which is not what I want to do.

Any ideas? Thanks in advance
Luke

 

[steve the fish]

I presume customer is in the data? Cant you just use the pivot report filter?

 

[OceanIT]

It is in the data. I need to see the overall Sales figures, then a column for just this one specific customer. So adding the filter will only show me the customer itself.

Basically I'm trying to show that this customer is say 75% of all product sales....

The only other idea I had was to put a duplicate pivot table to the side of my existing one, filter that to the specific customer, then hide the columns except the Sales Price sum. Only problem with this is it may not align if that customer hasn't ordered specific products.

 

[Kaps_mr2]

I'm guessing that QTY sold and sales price sum are COLUMN headings for your pivot report. You need to add customer a ROW heading for your report.

 

[Oeldere]

Probably you need to look in the second tab (on inputbox values) and select something like:

% off this row or % off this column.

 

[OceanIT]

I'm guessing that QTY sold and sales price sum are COLUMN headings for your pivot report. You need to add customer a ROW heading for your report.

both are Values (which is set to Sum) - Columns just shows "Values"

If I add Customer in as a row, it appears before the values and also shows ALL customers for that product. All I want is total sales (as the Pivot Table currently is) and a column for only this one specific customer. I'm trying everything to try and get a layout that suits the question I've been asked by our Director, nothing yet!

 

[steve the fish]

Basically I'm trying to show that this customer is say 75% of all product sales....

Wouldn't a SUMIF/SUM from the main data work for that?

 

[OceanIT]

Wouldn't a SUMIF/SUM from the main data work for that?

The SUMIF in the data table shows the right value. But the value appears in every row that matches. So when this field is added to the Pivot Table to the "Values" then it sums these all up, which is wrong.
If I use Average, the individual row values are correct, but the Row Total is wrong.

I'll try and get something attached to show exactly what I am doing

 

[Oeldere]

probably you get better help if you show an example of your data, and the desired result (without confidential information).

 

[steve the fish]

Yes but in a single cell away from the main data cant you do a calculation:

=SUMIF(blah blah)/SUM(blah blah)

This will produce result you require I believe.