Subtotal Percentage Calculation on a Row in a Pivot Table

[AustinLe]

Hello,

Attached is a sample file with a table of data as well as a pivot table. What I would like to do is have a new pivot column that calculates the percentage of the two previous columns of values. So in my example I would like column D to be a part of the pivot table but it should calculate B/C (B4/C4, B5/C5, B6/C6, etc). I want it to be a part of the pivot and not just a standard formula column because the slicer changes the size of the pivot and a standard formula column doesn't re-size dynamically as far as I know.

Add row percentage subtotals.xlsx

On a side note, how can the availability be more easily entered? is there a way to tell a pivot to use the value instead of sum, count, var, etc?? The availability hours are hard coded once for the week but don't change within the week.

[MarvinP]

Hi Austin,

See if the attached is what you want. It is a Calculated Field in the Pivot.

Also see: http://www.contextures.com/excel-piv...ted-field.html

[seasider89]

Click into Pivot Table Options on the ribbon.

Then Fields, items & Sets > Calculated Field

Enter a name for your field, then double click 'Job Time', enter a / then double click 'Availability'. Click add and you'll see your new field appear in the PivotTable field list. Drag this into your 'Values' box then highlight the field and format as a percentage.

[oeldere]

With a calculated field

excel 2013 => analyze, fields, items and settings

See the attached file.

[AustinLe]

MarvinP, seasider89, and oeldere,

Thanks for the lightning fast responses! MarvinP that is exactly what I was looking for. Seasider89, this is also very helpful information. Again, Oeldere very helpful. Thanks to all of you! Rep added

I edited my original post with another small question regarding the input of data that doesn't fit the table format. I will add a better sample file. If that's an easily answered question I'd love some help on it!

Add row percentage subtotals.xlsx

[seasider89]

I'm not sure exactly what you mean by "doesn't fit the table format" exactly??

[AustinLe]

Seasider,

It's hard to explain exactly what i mean but its a convenience issue more than anything. Try to imagine the data sheet spans many people over many weeks who work on multiple projects a week (the ID column B). It can invite errors if they were to assign someone availability twice in the same week and thus the pivot will be wrong. It seems odd to have to input a weeks of availability in a table that records the time it takes to do a project. I'm trying to find a simple way to combine the two tables in my second sample attachment. I hope this helps but i get the feeling it might not :P

[seasider89]

Yeah I'm still not 100% what you mean. If two tables works, I see no reason why not to do this.

[AustinLe]

I cant get them to connect (error: the tables cant connect because they are damaged) am i to be combining them with a relationship?

[oeldere]

in yellow the helpcolumns (which can be hidden).

in green the result.

see the attached file.

[AustinLe]

oeldere, that works. Thank you! Rep added and thread solved.