Pivot Table calculations

[salexander]

I am trying to calculate the % of a states various statuses automatically in the pivot table. I have tried the calculated field and it isn't calculating the correct data (or maybe I am using the wrong formula?). The simple % of Grand Total functions gives me the % of the particular status but not the overall grand total. See the hard coded column for what I am trying to get.

I am thinking some code might be needed??

[kirsten]

I am trying to calculate the % of a states various statuses automatically in the pivot table. I have tried the calculated field and it isn't calculating the correct data (or maybe I am using the wrong formula?). The simple % of Grand Total functions gives me the % of the particular status but not the overall grand total. See the hard coded column for what I am trying to get.

I am thinking some code might be needed??

Hi salexander,
Unfortunately, Excel is not very capable when it comes to calculated fields. The only thing it can do to underlying data is SUM it up, so you can't operate on a count based function. The best way to work around this limitation is to add 2 more columns to the right of your data table called "count_workorder" and "count_complete" and then populate with 1's and 0's depending on whether it is to be counted. Then, when Excel sums up the 1's it will be the correct count number.

If you need more help or have further questions, you should contact me at [email protected], or www.excelgirl.com/contact-us/