additional columns on a pivot that is added on

[groceryguy306]

Hello All

I have a question regarding a pivot table. The table created the way i want it, but now i want to add a column besides the table that also moves with the table when i expand and collapse the chart.

for example I have company 1, I have the pivot table sort from high to low according to the grand total of sales (column J)
now I have to have column K added beside the table with the formula "=i9/$j$8" so i can see the percentage of total sales each product give me for the company.
but i also want to be able to collapse that company field and expand another company 2 (located in row 19) and have the same sales percentage showing for company 2 and i do company 1.

am i going about this the wrong way?

Thanks In advance

 

[johnny51981]

Add the Dollars column to the Values section again (you will have 2 of them there now)
Click the carrot next to the newly added Dollars2 and select Value Field Settings.
Change the name in the Custom Name field (I named it % of Total Sales)
Go to the Show Values As tab and select "% of Parent Row Total" in the drop down.

Now, this will automatically give you a % of Total Sales column for each month that there is data. You can reorganize this to have all of your Dollar values in the front half, and your %'s in the back half but moving the "Values" component in the Columns section of your PivotTable set up to be above the Month.

Hopefully this makes sense and is what you are looking to achieve.

 

[groceryguy306]

Add the Dollars column to the Values section again (you will have 2 of them there now)
Click the carrot next to the newly added Dollars2 and select Value Field Settings.
Change the name in the Custom Name field (I named it % of Total Sales)
Go to the Show Values As tab and select "% of Parent Row Total" in the drop down.

Now, this will automatically give you a % of Total Sales column for each month that there is data. You can reorganize this to have all of your Dollar values in the front half, and your %'s in the back half but moving the "Values" component in the Columns section of your PivotTable set up to be above the Month.

Hopefully this makes sense and is what you are looking to achieve.

Thanks Johnny.
that is exactly what i was looking for.

 

[groceryguy306]

Add the Dollars column to the Values section again (you will have 2 of them there now)
Click the carrot next to the newly added Dollars2 and select Value Field Settings.
Change the name in the Custom Name field (I named it % of Total Sales)
Go to the Show Values As tab and select "% of Parent Row Total" in the drop down.

Now, this will automatically give you a % of Total Sales column for each month that there is data. You can reorganize this to have all of your Dollar values in the front half, and your %'s in the back half but moving the "Values" component in the Columns section of your PivotTable set up to be above the Month.

Hopefully this makes sense and is what you are looking to achieve.

This gives some different numbers as a percentage though. in my above mentioned sheet, the sales percentage is 26.75% (formula =j9/j8)
as i want to compare the sales of 17,335 of product a with the total sales of the company which is 64,812.

unless i did something wrong with your steps. the pivot table gives me a sales percentage ( % of parent row total) of 14.68%

 

[groceryguy306]

This gives some different numbers as a percentage though. in my above mentioned sheet, the sales percentage is 26.75% (formula =j9/j8)
as i want to compare the sales of 17,335 of product a with the total sales of the company which is 64,812.

unless i did something wrong with your steps. the pivot table gives me a sales percentage ( % of parent row total) of 14.68%

This gives some different numbers as a percentage though. in my above mentioned sheet, the sales percentage is 26.75% (formula =j9/j8)
as i want to compare the sales of 17,335 of product a with the total sales of the company which is 64,812.

unless i did something wrong with your steps. the pivot table gives me a sales percentage ( % of parent row total) of 14.68%

this reply needs to be deleted. it's no longer valid

 

[johnny51981]

Yay!!! I'm glad that it worked out! (It worked out, right? HA)