Pivot Table Calculated Field on a specific value

[purplefox]

Hi, I'm not sure how to search for a solution to this problem but I'm hoping one of you good fellows on here can help.

I have a set of data where monthly new data is added as new rows into a table, one of the columns denotes the year of the data (there is month information too). I take this large data table and pivot it to show certain parameters and in doing this split the sales values by year adding the year column to the columns area of the pivot table fields selector. I've created a very simple example version as a picture attached.

So I have value columns Yrs 2020, 2021, 2022, but say 2022 is a YTD value showing 9 months, I want to calculate a new column to take 2022 value, divide by 9 and multiply by 12, within the pivot table. If I do this with calculated field using an if statement to only apply to 2022 I get a column for each year with non 2022 years showing zero values. is there a way to either

•apply the calculated field to only perform the calculation on the pivot column for 2022
•Or to hide the additional zero value columns without having to just hide the actual column?

many thanks in advance,

thanks

Andy

 

[purplefox]

Ok, so I will now answer my own query, feel like it's quite simple, but not entirely intuitive.

The answer is calculated Item, of course it always seems greyed out and I didn't realise (or had forgotten) that to get to calculated item you need the to go the the menu option when a cell with either column or row "Item" is selected. I googled "Why is the calculated item greyed out".

Thanks Anyway

Andy