# Division of totals/subtotals



## BizAnalyst916 (May 11, 2012)

I am using Excel 2010 with PowerPivot on Win XP Pro SP3

I have a PivotTable that looks like:

Month.....Day....Col1.....Col2.....Avg of Col1 / Col2
*Feb.................517.....566.................91.44%*
.............27......169......193..................87.56%
.............28......172......191..................90.05%
.............29......176......182..................96.70%

The underlying table has a calculated field that divides Col1 by Col2, and that calculated field is in the Values Field, summarized by Average.

The detail lines (days) are exactly correct, as you can see: 

(on the 27th): 169/193 = 87.56%

The Feb subtotal does not calculate the way I want it to, however. I want it to be:

517/566 = 91.34%

Instead, it is taking the simple average of the calculated field results from each day in Feb:

(87.56 + 90.05 + 96.7) / 3 = 91.44%

Is there a way to get around this and create a calculated field within the PivotTable that will evaluate totals and subtotals the same way it does the detail rows?


----------



## powerpivotpro (May 11, 2012)

Yes, absolutely.  First off, you don't need the calc column at all.  

Write 3 PowerPivot measures instead, in the pivot:

[M1] = SUM(Table[Col1])
[M2] = SUM(Table[Col2])
[M3] = [M1] / [M2]

I think [M3] is what you want.

Later, if you want to override totals and subtotal behavior to do more specific things, see this post:

http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/


----------



## BizAnalyst916 (May 14, 2012)

Thank you for the reply, Rob.  I didn't even know about measures.  I'll probably actually start using PivotTables now!


----------



## powerpivotpro (May 14, 2012)

Measures are probably THE biggest feature in PowerPivot.  They will quite literally change the way you think about your analysis and reporting, for the better.  Enjoy!


----------



## StacyMag (Aug 4, 2015)

_"Yes, absolutely.  First off, you don't need the calc column at all.  

Write 3 PowerPivot measures instead, in the pivot:

[M1] = SUM(Table[Col1])
[M2] = SUM(Table[Col2])
[M3] = [M1] / [M2]

I think [M3] is what you want.

Later, if you want to override totals and subtotal behavior to do more specific things, see this post:

Subtotals and Grand Totals That Add Up “Correctly” - PowerPivotPro PowerPivotPro"_


YOU ARE MY HERO!!!! I am brand new to PowerPivot, and creating some new reports for the boss. I have been searching the internet for at least 2 weeks trying to figure out how to get my subtotals to properly calculate my division formula! I was able to do it as a calculated field in regular pivot-table, but I have some Slicers I want to add and do stuff with, and PowerPivot seemed to be the way to do that.  I was seriously going to give up and go back to plain PivotTable until I found this!! THANK YOU!


----------

