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.
