summing, grouping percentages in pivot table.

[mje1975]

There must be an easy solution to this, but I cannot find one. I'm
having the basic problem of summing and grouping percentage values in
a pivot table. For example, with data like this:

Dept, Premium, Commission, Commission_Pct
Liability, 40, 4, 0.1
Property, 10, 2, 0.2

Commission_Pct is simply Commission/Premium.

I want to create a pivot table that shows displays premium and
commission_pct, then sums the totals. Of course, when I set up the
table, and sum the values, premium sums to 50, but commission_pct sums
to .3 (when it should be 6/50 = 0.12). I tried using Commission
instead of Commission_Pct, then playing with the options in the field
to try and display as a percent of another column (in this case I
would want to show as percent of premium). However, nothing seemed to
work. I was able to show Commission as a percent of the total
Commission, but that is not what I want. I want to show Commission as
a percent of Premium in the pivot table.

There must be any easy fix that I am missing. Thanks for your help.

- Matt

 

[smartin]

There must be an easy solution to this, but I cannot find one. I'm
having the basic problem of summing and grouping percentage values in
a pivot table. For example, with data like this:

Dept, Premium, Commission, Commission_Pct
Liability, 40, 4, 0.1
Property, 10, 2, 0.2

Commission_Pct is simply Commission/Premium.

I want to create a pivot table that shows displays premium and
commission_pct, then sums the totals. Of course, when I set up the
table, and sum the values, premium sums to 50, but commission_pct sums
to .3 (when it should be 6/50 = 0.12). I tried using Commission
instead of Commission_Pct, then playing with the options in the field
to try and display as a percent of another column (in this case I
would want to show as percent of premium). However, nothing seemed to
work. I was able to show Commission as a percent of the total
Commission, but that is not what I want. I want to show Commission as
a percent of Premium in the pivot table.

There must be any easy fix that I am missing. Thanks for your help.

- Matt

Mi Matt,

If your data includes a pre-calculated Commission_Pct, I suggest you /do
not/ include that in your pivot table source data (or at least ignore it).

Instead, since you can easily derive this value from the other data,
bring Premium and Commission into the pivot source data, then create a
calculated field.

The best way I know to explain how to do this is to tell you to add the
PivotTable tool bar first. Then you can select Pivot Table | Formulas |
Calculated Field. Insert your formula as Name "Commission Percent",
Formula "=' Commission'/' Premium'". It's all point and click here.
Clicking OK will add the calculated field to your report, and if I have
given proper instruction, you will see it calculates as expected.

 

[mje1975]

That did it. I knew it was something easy. Thanks alot. - Matt