Solved: Percent of Row Group in Pivot Table?

[Paul_Hossler]

I can do what I need to do with VBA or with formulas, but it'd be a lot easier if I can configure the PT and let Refresh do it

I added a 'Sum Of' data field and a '% of Total' data field

I'd like to use a computed PT field to calculate the % that each line is within its group, or any other method

Any help / ideas appreciated

Paul

[Bob Phillips]

Paul,

Just drag Field 4 into the value pane again; right-click that item in the pivot; select 'Value Field Settings'; select the 'Show Values As' tab' and select '% of Parent Row Total' from the dropdown.

[Paul_Hossler]

Ah, ha - thanks,

The only concern that would probably raise questions is that the Subtotal for the '% of Parent Row Total' doesn't add to 100%; it's the same as the '% of Total' for that row value.

Any way to fix that issue by making it = 100% or just hiding it altogeather??

Otherwise, this is still way better than the manual worksheet formulas I had been using

Paul

[Bob Phillips]

Not really Paul as that total is aggregating all items under the parent row, and for the Field 1 items, the parent is the grand total, so you are seeing that as the percentage of the grand total.

You could get rid of subtotals for the Field 1 level, select say AAA, right-click and click the Subtotal 'Field 1' item. But, that will remove the subtotals for all columns at that level.

Another way is to set the font colour the same as your cell background, but that is not so good with dynamic pivots.

[Paul_Hossler]

Sort of expected that

Thanks

Paul