Pivot Table include values and % Of

[martipe1]

I have created a Pivot Table for sales comparison purposes, I summarize Units and $ and group them together by Budget, Last Year and Actual.

I can see the numbers but if I want to make a comparison and I choose % OF, the values automatically are replaced by the %.

Is it possible to create a pivot table that can have values and the %OF, comparing Units and $ actual vs. Last Year and budget in the same table?

I have done it manually in the past but it is too much work, that is the reason I prefer to use a Pivot Table (the number of records I have are more than 30,000)

Thanks

 

[pavin]

Yes it is possible through Calculated field. Check the below link for Video explanation of a calculated field.

http://www.datapigtechnologies.com/flashfiles/pivot4.html

Hope this helps

 

[martipe1]

Praveen,

Thank you for your answer.

I included two calculated fields and made them equal to $ and Units and modify those fields to become %OF, that solved my problem.

Is there any way to replace the annoying #DIV/0! in the %OF field?

Thanks,

 

[pavin]

Use a 'IF' condition & 'ISERROR' in the Calculated Field. For example

IF(ISERROR(%OF),"",%OF)

Hope this helps.

 

[martipe1]

Praveen,

Thanks, as an alternative option in Pivot Table, Table Options I checked "For errors values, show:" and left it blank.

Once again thank you