Subtotal based on Category

barjoman

Board Regular
Joined
Oct 29, 2014
Messages
99
Hello everyone,

I have an issue which I'm hoping can be solved by PowerPivot. My sample data set is below:

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: center"]Region[/TD]
[TD="class: xl63, width: 64, align: center"]Category[/TD]
[TD="class: xl63, width: 64, align: center"]Attempts[/TD]
[TD="class: xl63, width: 64, align: center"]Success[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]North[/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]100[/TD]
[TD="class: xl63, align: center"]50[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]North[/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]100[/TD]
[TD="class: xl63, align: center"]60[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]North[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]50[/TD]
[TD="class: xl63, align: center"]30[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]North[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]50[/TD]
[TD="class: xl63, align: center"]40[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]South[/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]500[/TD]
[TD="class: xl63, align: center"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]South[/TD]
[TD="class: xl63, align: center"]A[/TD]
[TD="class: xl63, align: center"]500[/TD]
[TD="class: xl63, align: center"]300[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]South[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]200[/TD]
[TD="class: xl63, align: center"]200[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]South[/TD]
[TD="class: xl63, align: center"]B[/TD]
[TD="class: xl63, align: center"]200[/TD]
[TD="class: xl63, align: center"]150[/TD]
[/TR]
</tbody>[/TABLE]

And my resulting Pivot Table is as follows:

[TABLE="class: outer_border, width: 403"]
<tbody>[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]North[/TD]
[TD="align: center"][/TD]
[TD="align: center"]South[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Values[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]Attempts[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]400[/TD]
[/TR]
[TR]
[TD="align: center"]Success[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]350[/TD]
[/TR]
[TR]
[TD="align: center"]Success Rate[/TD]
[TD="align: center"]55.00%[/TD]
[TD="align: center"]70.00%[/TD]
[TD="align: center"]40.00%[/TD]
[TD="align: center"]87.50%[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, I have the Region and Category as Columns, and I'm displaying the sum of Attempts, Successes, and the Success Rate Measure as Values in the Rows. There are no Subtotals or Grand Totals.

What I'm trying to achieve is a Subtotal at the right of the Pivot Table by Category. I would like it to end up looking like this:

[TABLE="class: outer_border, width: 485"]
<tbody>[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]North[/TD]
[TD="align: center"][/TD]
[TD="align: center"]South[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Values[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]A Total[/TD]
[TD="align: center"]B Total[/TD]
[/TR]
[TR]
[TD="align: center"]Attempts[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]500[/TD]
[/TR]
[TR]
[TD="align: center"]Success[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]350[/TD]
[TD="align: center"]510[/TD]
[TD="align: center"]420[/TD]
[/TR]
[TR]
[TD="align: center"]Success Rate[/TD]
[TD="align: center"]55.00%[/TD]
[TD="align: center"]70.00%[/TD]
[TD="align: center"]40.00%[/TD]
[TD="align: center"]87.50%[/TD]
[TD="align: center"]42.50%[/TD]
[TD="align: center"]84.00%[/TD]
[/TR]
</tbody>[/TABLE]

As far as I know, it is not possible to do this through the Pivot Table, which is why I'm hoping there is some magic I can do through Power Pivot.

Thanks in advance for your help.

Ben
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
On PivotTable Tools/Analyze/OLAP Tools/Convert to Formulas.
Later, you can move the columns to the appropriate position
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,708
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top