Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing, grouping percentages in pivot table.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing, grouping percentages in pivot table.
mje1975 wrote:
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing, grouping percentages in pivot table.
That did it. I knew it was something easy. Thanks alot. - Matt
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Percentages | Excel Discussion (Misc queries) | |||
Pivot Table Percentages | Excel Worksheet Functions | |||
Pivot Table Percentages | Charts and Charting in Excel | |||
Pivot Table Percentages | Excel Discussion (Misc queries) | |||
Pivot Table percentages | Excel Discussion (Misc queries) |