Division of totals/subtotals

[BizAnalyst916]

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]

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]

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

 

[powerpivotpro]

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]

"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!