98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » PowerPivot pivot table calculation/ total column | Excel forum
PowerPivot pivot table calculation/ total column | Excel forum
Resolved · Medium Priority · Version 2013
Adam has attended:
Excel PowerPivot course
PowerPivot pivot table calculation/ total column
Hi,
I have produced a pivot table from PowerPivot based on two tables of different data with a relationship between the two,
i.e. sales of product x and product y by customer.
The pivot table shows columns for sum of x and sum of y by customer but doesn't show a total column for x + y by customer and the Calculated Field is not available in Calculations.
I'm not sure I can do a simple Sum function in the tables to create a new column as there are various rows for each customer.
Is there a simple way to just add a total column to a produced pivot table?
Let me know if you need any further info!
Thanks
Adam
RE: PowerPivot pivot table calculation/ total column
Hi Adam,
Thank you for the forum question.
You will need to create a calculated column in the many table in PowerPivot or a measure.
If you want to create a calculated column.
Type:
=["The name of the column in the many table"] + Related(["the name of the column in the one table"])
If you want to create a measure:
Type in the measure area under the many table:
Total:=Sumx("Name of the many table",["The name of the column in the many table"] + Related(["the name of the column in the one table"]))
Refresh the PivotTable and you will find the Total as a new field you can add to the PivotTable.
Kind regards
Jens Bonde
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
Tue 23 Aug 2016: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Conditional formatting for cells that return text , not picked up by Go to commandIf you have tried to format all cells containing text even those that display text as a result of a formula you may have had difficulty. As Go to command with constant selected does not pick up formulas that result in text. |