Excel - How to Calculate Percentage in a Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Pivot table. First category is account, under that is maturity year for a bond, and under that is the month of the maturity. The cash values for the account as a whole, the amount for the specified year, and the specified months are all listed in the column to the right. I need to figure out how to calculate the percentage of cash in in each year compared all to the years and the percentage of cash in one month when compared to all the months. I titled the cash column as "Amount". I was using a calculated field but I cannot specify using "Amount for Year"and "Amount for Month".
Solved by V. B. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/09/2018 - 05:46
Greetings, welcome to Excel Chat
Excelchat Expert 17/09/2018 - 05:46
I understand that you are looking for help with Pivot Tables
User 17/09/2018 - 05:46
Yes
Excelchat Expert 17/09/2018 - 05:47
Before we begin, I would like to inform you that our policy allows us to help you with one problem in one session
User 17/09/2018 - 05:47
Yes this is my only problem. I am setting it up in the spreadsheet so you can have a better idea
User 17/09/2018 - 05:48
1
Excelchat Expert 17/09/2018 - 05:48
Yes please create a table for me so that I can convert that into a pivot table for you
Excelchat Expert 17/09/2018 - 05:49
Once you add data to the spreadsheet, please let me know, I will download it at my end
User 17/09/2018 - 05:52
All set. On the right is the raw data. On the left is how I would like the pivot table to function. The percents for the year are out of the total amount in the account. The percents for the months are out of the total amount for the year. Hope that helps
Excelchat Expert 17/09/2018 - 05:53
Thanks for this, please allow me sometime to work on it
User 17/09/2018 - 05:54
Will do
Excelchat Expert 17/09/2018 - 05:54
Appreciate your patience
Excelchat Expert 17/09/2018 - 05:57
Please have a look
Excelchat Expert 17/09/2018 - 05:57
[Uploaded an Excel file]
Excelchat Expert 17/09/2018 - 05:57
For creating two views in one Pivot table (I added additonal similar column in raw Data Amount2)
Excelchat Expert 17/09/2018 - 05:58
Now in Sheet "Pivot" I have used this Amount2 to create percentages
Excelchat Expert 17/09/2018 - 05:58
and Amount to calculate absolute amount
Excelchat Expert 17/09/2018 - 05:58
Please refer to "Pivot" sheet in the workbook
Excelchat Expert 17/09/2018 - 05:59
I hope this is what you are looking for
Excelchat Expert 17/09/2018 - 06:00
Are we connected?
User 17/09/2018 - 06:00
Yes we are connected. Trying to figure out what you did
Excelchat Expert 17/09/2018 - 06:01
Shall I help you with the steps?
User 17/09/2018 - 06:01
Yes please. I am doing this with quite a few accounts which is why this has gotten a little bit difficult
Excelchat Expert 17/09/2018 - 06:01
Not a problem
Excelchat Expert 17/09/2018 - 06:02
Are you clear with the reason why I add Amount2 column?
User 17/09/2018 - 06:02
No
Excelchat Expert 17/09/2018 - 06:02
Ok
Excelchat Expert 17/09/2018 - 06:03
So basically you want two columns in Pivot from the same column
Excelchat Expert 17/09/2018 - 06:03
But how pivot works is that it can use one column only once
Excelchat Expert 17/09/2018 - 06:03
So we created same column again so that we can use one column for number value and another for percentage
User 17/09/2018 - 06:04
Got it. So how do we turn amount2 into a percentage?
Excelchat Expert 17/09/2018 - 06:04
Yes that I will tell in next step
Excelchat Expert 17/09/2018 - 06:04
So once we create a basic pivot table
Excelchat Expert 17/09/2018 - 06:05
Add amount2 in one of the columns of the pivot, it will look something like this
Excelchat Expert 17/09/2018 - 06:06
[Uploaded an Excel file]
User 17/09/2018 - 06:06
Got it
Excelchat Expert 17/09/2018 - 06:06
Now right click on the column Amount2 in Pivot table
Excelchat Expert 17/09/2018 - 06:07
and select Show Value as > Percentage of parent row total
Excelchat Expert 17/09/2018 - 06:07
It will convert the numbers into desired percentages
User 17/09/2018 - 06:07
Great. I get it. Thank you for the Help
Excelchat Expert 17/09/2018 - 06:07
Happy to help :) Hope my explanation was of help
Excelchat Expert 17/09/2018 - 06:08
Kindly rate the session, it helps us
Excelchat Expert 17/09/2018 - 06:09
Have a good day!
Excelchat Expert 17/09/2018 - 06:10
Kindly end the session

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.