Pivot Table Calculated Field

[deand]

I have created a pivot table that looks like this:

Training Program Total Number of People Assigned Number of People Already Completed

I need to show the percentage acquired (completed/total number)

I know this should be simple, but I'm not very skilled in pivot tables and I'm really struggling.

Can anybody offer advice?

 

[RoryA]

Welcome to the forum.

Are your Number fields using the Count function? Assuming they are, you can't use that in a calculated field - they always SUM. You would need to add 2 new columns to your source data with 1 for each row (so you can sum it to get an overall count) and 1 for those rows that are completed. You can then create a calculated field that simply divides one by the other.

Note: I am making some assumptions about your data layout - it may be possible to simply use the display options for the completed field to show it as the % of the total.

 

[deand]

Hi Rory
Thank you for your response! I'm not sure if I fully understand how to do this.
I currently have 2 column values (as you guessed above), Count of Assigned, Count of Completed
I tried to add these same items as values with 'sum of' instead, but I got really weird numbers (6 assigned became 0' 5 completed became 211552).

 

[RoryA]

You will need to add two new columns to your actual source data. One of them should just return 1 for every row. You can then add that to the pivot table and sum it to get an effective count. The other column should return 1 if the row is completed. Then, again, you can sum this to get a completed count.

Once you have those two new fields in the pivot table, you can create a calculated field that divides the new completed count by the new total count.

 

[deand]

Stellar! Worked like a charm. Thanks so very much, Rory.