Percentages in Pivot Tables

[AllanKealey]

I am new to Pivot Tables and am having a problem with percentages.
I have a Pivot table which looks like this

Block Incidents Cumulative Incidents People Cumulative People Percentage Answer should be
1 47 47 32 32 23.15% 68.09%
2 37 84 28 60 18.25 % 71.43%
etc
Total 203 140 61.33%

The percentage is the Cumulative People divided by Cumulative incidents. I don't know where the 23.15% is coming from and want the correct answers within the pivot table so I can draw a graph. I seem to have tried every option and read numerous articles but nothing seems to address this issue.
Amy ideas? I am using Excel 2016 and Office 365

Thank in advance

 

[AllanKealey]

I didn't realise the formatting of the message would mess up the table in my original post so here is a better version that might make more sense. I have highlighted the values which I need evaluated as a percentage. The correct answer, highlighted in yellow, is outside the pivot table but I would like that to be inside the pivot table so I can draw a graph from it. The data in the pivot table will be amended over time by adding more Blocks so I don't want to rely on manually creating the correct answers.
Thank you

<tbody>
[TD="align: center"]Block[/TD]
[TD="align: center"]Incidents[/TD]
[TD="align: center"]Cumulative Incidents[/TD]
[TD="align: center"]People[/TD]
[TD="align: center"]Cumulative People[/TD]
[TD="align: center"]Percentage[/TD]
[TD="align: left"][/TD]
[TD="align: center"]Answer Should be[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]23.15%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]68.09%[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]18.25%[/TD]
[TD="align: left"][/TD]
[TD="align: center"]71.43%[/TD]

[TD="align: center"]etc[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]Total[/TD]
[TD="align: left"][/TD]
[TD="align: center"]203[/TD]
[TD="align: left"][/TD]
[TD="align: center"]140[/TD]
[TD="align: center"]61.33%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

 

[James006]

Hello,

Should you need to dig into Calculated Fields in a Pivot Table ...

see :https://www.contextures.com/excel-pivot-table-calculated-field.html

Hope this will help

 

[AllanKealey]

Hello,

Should you need to dig into Calculated Fields in a Pivot Table ...

see :https://www.contextures.com/excel-pivot-table-calculated-field.html

Hope this will help

Thanks for this but I cannot get it to work within the context of a pivot table.
The data that drives the table is located on another tab and this will be added to over time adding extra blocks to the pivot table. These need to be calculated as well
Ideally I need someway of referencing the contents of the pivot table so I can calculate the percentages as I would do in a normal spreadsheet. The values in the pivot table are sums and counts from the data on the other tab so don't seem to appear in the PivotTable Fields list.. Is there a way of referencing the rows in the table that holds the data like you could do with a range?

 

[SpillerBD]

I recommend going full-dive into Power Pivot. Its the best method for reliable results.
Your immediate source will be Mike Girvin's YouTube Channel.

Check out Magic Trick 1395

Long term, besides Mike's PowerBI/PowerPivot/Data Analysis videos will be PowerPivotPro Blog and Rob Collie's and Avi Singh books. The "spaniards" as you'll get to know, Alberto Ferrari & Marco Russo, will also become a primary source.

 

[AllanKealey]

I recommend going full-dive into Power Pivot. Its the best method for reliable results.
Your immediate source will be Mike Girvin's YouTube Channel.

Check out Magic Trick 1395

Long term, besides Mike's PowerBI/PowerPivot/Data Analysis videos will be PowerPivotPro Blog and Rob Collie's and Avi Singh books. The "spaniards" as you'll get to know, Alberto Ferrari & Marco Russo, will also become a primary source.

Thank you, I will give them a look

 

[BGY23]

Hi,
Remove the percent you have in the table I suspect its just the sum of the percentages in your source data.

Insert a calculated field into the pivot table using the formula Cumulative people/ cumulative incidents.

This will give the percentages you're expecting or t least it di for me

 

[AllanKealey]

Hi,
Remove the percent you have in the table I suspect its just the sum of the percentages in your source data.

Insert a calculated field into the pivot table using the formula Cumulative people/ cumulative incidents.

This will give the percentages you're expecting or t least it di for me

Thank you

This is what I have been trying to do but the Columns in the pivot table don't appear in the calculated field; field selection window. How do I get them there? I have tried making a pivot table using the original table as the source but that doesn't seem to work

 

[SpillerBD]

Another video from Mike, https://www.youtube.com/watch?v=vpavH7P-Nhs

He hits many of the important items of Pivot Tables, like Row/filter context and by using Measures, you maintain control of that context which is where % calculations get messed up from our expectations and the way PivotTables work.

 

[BGY23]

If you PM me your email I'll send the workbook I made. If you don't want to do that its ok. The calculated field should go in the value area of the pivot table. I made it work twice now.