Forum Discussion
garyljy_li
Jan 25, 2019Copper Contributor
Performing Calculations on One Column in Pivot Table
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.
No RepliesBe the first to reply