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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

A
  • Solved
Power Query to arrange 8 Different Headers as two different columns
Replies
5
Views
305
ashish128
A
A
  • Solved
Power Query - filter & lookup values based on another table
Replies
7
Views
3K
Automatrix
A
goko
  • Question Question
Evaluation Scenario - Power Query
Replies
1
Views
688
goko
goko
J
  • Solved
Subtotals within Sumifs range
Replies
6
Views
981
jaz44
J
S
  • Question Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
365
strugglingaccountant
S

Forum statistics

Threads
1,224,154
Messages
6,176,731
Members
452,740
Latest member
MrCY

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