Pivot Table calculated Column Query

[gmazza76]

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?

thanks in advance
Gavin

 

[bbotzong]

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.

 

[gmazza76]

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

thanks

 

[bbotzong]

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]

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%

Sorry if this sounds a really easy solution

thanks
Gavin

 

[bbotzong]

I'd have to see your numbers source... sorry.