Home

Performing Calculations on One Column in Pivot Table

garyljy_li
Occasional Visitor

Hi everyone,

 

I am having difficulties trying to perform calculations to a particular column within my pivot table. As seen in the picture, I would like to embed additional columns to the right of the PT so that it calculates the difference of "Jan 2019" to the previous month, "Jan" compared to the MAX of the entire row, and "Jan" compared to the AVERAGE of the past 12 months.

 

I have tried using "Calculated Fields", but it seems like there are limitations to this function - it asks me calculate the whole field rather than allowing me to select and perform calculation on a particular column ("Jan"). I also tried clicking "Calculated Items," but it pops up an error message saying that my report field is grouped, thus cannot calculate - each of the months aggregates multiple days of data.

 

I also tried to do calculation outside of the PT by disabling GETPIVOTDATA, so that I can reference the cells in the PT. However, the calculation isn't dynamic - the added columns won't automatically expand/contract whenever new data is added into the PT.

 

Is there a way to add these calculation within the PT, so that the calculation is dynamic?

Much thanks to everyone.Capture.PNG

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies