This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello.
I have being searching for a solution to this problem for a long time without finding a good solution.
I have a pivot table that shows the total sales per customer and month.
The months are displayed as columns.
I need to add a column that will show the ratio "Customer SAles Total per year" / "Total Sales(all years)".
How can I add this column to the pivot table?
The test excel file can be found here:
Thank you for your help,
J.C.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Sample Solution for you
Add a Calculated field from the PivotTable Options Tab
=Sales
Click on the new column and Show Values as > % of Column Total
Final Result
Hello,
Thanks for your reply.
I have tested something similar to your suggestion (you can find my test on the worksheet called "Sheet1") but my problem is adjusting the column to keep up with an uncertaing number of rows!!
I could apply the formula to entire column but that is king of an hack rather than a good, simple solution...
Do you know how to dynamically apply a formula to the new rows that will be added by the pivot table (as we add more data to the data entry table)?
Thank you,
J.C.
Hello,
Thanks for your reply.
Everytime I add a "Calculated field" Excel adds a new column for every month showing percentage...
But the columns under Month must only show the absolute Sales value.
Please take a look at the image posted by Bernard Liengme; notice that percentage column should be displayed only at the end (right side) of the pivot table.
Thank you,
J.C.
I did it without a PT field
best wishes
Hi, look into Debra web with videos and explanations.