**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.*