Pivot Table calculated Column Query

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
643
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I have a pivot table displaying information from Power Pivot as per below, but I want to add 3 columns to the table.
I would normally add a calculated columns through Analyze - "Fields, Items & Settings", but all the options are greyed out.

How would I add a column in so I can do these calculations?

1605105883914.png


thanks in advance
Gavin
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

bbotzong

Board Regular
Joined
Dec 17, 2003
Messages
56
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
When you created your Pivot Table, you *probably* had the box checked for Add This Data to the Data Model. By doing that, you disable the calculated fields in Excel and move that function to the Data Model. To add your calculated fields, on the Power Pivot tab in the Data Model group the Manager tool (if you don't have Power Pivot enabled, you can look on the Data Tab in the Data Tools group for a green icon on the lower right Go To The Power Pivot Window). Once in the Data Model, go to the empty column on the right and put in your formulas. If you are putting in aggregate function, go below the column you want to use (for example if you want an average, below the quantity column click in the Home Tab, Calculations Group, AutoSum) and choose Average. That will add a Measure that will then appear in your Pivot Table fields.

Hope this helps.
 
Solution

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
643
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

Thanks for the info above @bbotzong it seems to have worked, but a quick question if you don't mind?
In the columns below I have the % showing as the figure as a % of column totals.
Volume difference is the actual difference of the 2 columns 09/11/20 & 16/11/20 which is correct.
But how do I get the Movement % to show as a % and not as a number as I cant for the life of me get it to show as 0.13% which is what it should be

1605713423851.png


thanks
 

bbotzong

Board Regular
Joined
Dec 17, 2003
Messages
56
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
It appears you didn't divide the Movement% number by whatever you're comparing it to. Check the formula for the movement % column for a denominator.
 

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
643
Office Version
  1. 365
Platform
  1. Windows
Morning @bbotzong, what do I need to check the formula in movement % for?
Sorry I don't really use pivot tables. As the figures are based on call type (ROW LABEL) when I do the amendment it only allows me to do it against the "Row Label" and not the Tuesday -7 (1.01%) or the Tuesday Current (1.17%). So the movement should be 0.11%

1606123575842.png


Sorry if this sounds a really easy solution

thanks
Gavin
 

bbotzong

Board Regular
Joined
Dec 17, 2003
Messages
56
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
I'd have to see your numbers source... sorry.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,680
Messages
5,637,745
Members
416,982
Latest member
lisam77

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