Pivot Table - Formula's outside of PivotTable?

[gmooney]

I have a pivot table that I am unable to make a calculated field work in the pivot table (maybe due to my lack of using pivot tables). So I created the formulas in the first column to the right of the pivot table.

This would be acceptable except for when the user changes 1 of the 2 filters the formula rows might not line up correctly to the corresponding pivot table rows.

I then thought if I used the getpivotdata formula that would ensure that the formulas would follow to the correct row BUT again, even though the formula might have the correct result if the row changed, the formula row won't change?

Any thoughts?

Below is a print screen because I can't figure how the new Mr. Excel Capture

The column with the 77.7% is the column that is NOT a part of the pivot table. The formula for that is simply the 20.7/11.6-1. The formula will always be Brand 1/Brand 2. In this example under Bag Small there is no Brand 2 som. no calculation and it Clamshell Small disregard the 4.5oz item

 

[Louis_Guionneau]

you may need to create a measure to get % / divide. You can create a measure in power pivot.

 

[gmooney]

you may need to create a measure to get % / divide. You can create a measure in power pivot.

Hi Louis....I am not sure what you mean?

 

[Louis_Guionneau]

No problem! Do you know what power pivot is? Pivot tables are limited in what they can do and after my brief reading of your problem I think you'll need to use power pivot to set up the analysis you'd like to do in a scalable way (pivot tables don't really do divide well).

I'd be happy to help guide you (and maybe other members will step in too if I'm misunderstanding).

 

[gmooney]

No problem! Do you know what power pivot is? Pivot tables are limited in what they can do and after my brief reading of your problem I think you'll need to use power pivot to set up the analysis you'd like to do in a scalable way (pivot tables don't really do divide well).

I'd be happy to help guide you (and maybe other members will step in too if I'm misunderstanding).

Hi Louis...I have heard of PowerPivot before but never used it. The quick question that comes to mind with it is that this file that the PivotTable is in is currently being shared with about 70 users. Does whatever I do in my file with PowerPivot transfer within the file to them?

 

[Louis_Guionneau]

Yes, the PowerPivot is within the excel, so you should be OK on that front.

To set up the measure, you'll need to make sure that PowerPivot is activated as an add-on. Then add your raw data as tables to your PowerPivot - these will then update automatically if your data changes. then go into powerpivot and create a new measure in the calculated measures section within powerpivot to do the division function that you need. You can then add this to the pivot table. There should be a bunch of youtube videos which you can use to show you how to do this in more detail.