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
