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
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
GF Percentage Variance.xlsx
