Percentages in Pivot Tables

AllanKealey

New Member
Joined
Apr 12, 2019
Messages
5
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

AllanKealey

New Member
Joined
Apr 12, 2019
Messages
5
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

BlockIncidentsCumulative IncidentsPeopleCumulative PeoplePercentageAnswer Should be
14747323223.15%68.09%
23784286018.25%71.43%
etc
Total20314061.33%

<tbody>
</tbody>
 
Last edited by a moderator:

AllanKealey

New Member
Joined
Apr 12, 2019
Messages
5
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,706
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

New Member
Joined
Apr 12, 2019
Messages
5
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

Well-known Member
Joined
Aug 13, 2008
Messages
515
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

New Member
Joined
Apr 12, 2019
Messages
5
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,706
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

Well-known Member
Joined
Aug 13, 2008
Messages
515
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,369
Messages
5,414,047
Members
403,514
Latest member
Vivek pare

This Week's Hot Topics

Top