Excel 20

Resolved · Low Priority · Version 2010

Mon 18 Nov 2013, 10:20 Delegate Jason said...

Jason has attended:
Excel Advanced course
Excel Intermediate course
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - Formulas & Functions course

Excel 20

Hi,
question on how to use Calculated field in Pivot tables.

simple sheet in columns across, Revenue, COGS, Gross Margin and GM% by Sales order numbers.

ORDER REV COGS GM GM%
ABCD 100 75 25 25.0%

Each sales order is made up of multiple line items with different margins and GM%.

I am using the GM% to display Average of GM but i don't think this is arithmetically correct.

I want the calculated field to be the overall sales order GM%.

Many thanks
Jason

Mon 18 Nov 2013, 20:09 Edited on Mon 18 Nov 2013, 20:10 Trainer Gary said...

RE: Excel 20

Hi Jason

Thanks for getting in touch. I wasn't quite sure what you meant by "Average of GM" but I've enclosed a suggestion in an attached file.

It hasn't used a calculated field, but instead asking the PivotTable to render the value as a percentage instead of its number.

Have a look and see if this is any closer to what you were after.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector