Pivot grand totals grouped by kind of value

futureexcelpro

New Member
Joined
Jun 7, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

I have a problem with showing a customized grand totals in a pivot table.

I would want the pivot table to show me the periodic values for two kinds of value, both forecast and budget, as well as in the final columns to show the grand total for the both kind of value, forecast and budget. How pivot automatically does this is by calculating the grand total by combining forecast and budget values (in my example below the grand total would be 30, even though I would want 10 and 20).

If I would not want to look at the period values, pivot would automatically give me the totals in the form I would like (10 and 20). But the problem is, that I would like to have periodic values and their grand total value in the same table, and also divide the values to forecast and budget values, as shown in the table below.

Would you have any ideas to solve this problem? I really appreciate any help with this, thank you already in advance.

What I want:
P1P2P3P4Grand total
ForecastBudgetForecastBudgetForecastBudgetForecastBudgetForecastBudget
243645151020

What Pivot gives me right now:
P1P2P3P4Grand total
ForecastBudgetForecastBudgetForecastBudgetForecastBudget
2436451530
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Turn off the grand totals, then right click the field that is Budget/Forecast and choose Field Settings, then change the Subtotals setting to Custom... and Sum.
 
Solution

Forum statistics

Threads
1,136,969
Messages
5,678,890
Members
419,787
Latest member
juanam

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
Top