Subtotal based on Category

B

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.
Sort by date Sort by votes
On PivotTable Tools/Analyze/OLAP Tools/Convert to Formulas.
Later, you can move the columns to the appropriate position
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
I've forgotten something basic (or it cant be done!)
Replies
5
Views
128
pjmorris
P
A
  • Solved
Power Query to arrange 8 Different Headers as two different columns
Replies
5
Views
328
ashish128
A
A
  • Solved
Power Query - filter & lookup values based on another table
Replies
7
Views
4K
Automatrix
A
goko
  • Question Question
Evaluation Scenario - Power Query
Replies
1
Views
705
goko
goko
J
  • Solved
Subtotals within Sumifs range
Replies
6
Views
1K
jaz44
J

Forum statistics

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

Share this page

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