Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help with Pivot Table calculated field

0

Hi: 

Ive attached a file . . .

i'm trying to add calculated fields that show 2019 vs. 2018, 2020 vs 2019, and 2020 vs 2019. 

Would appreciate any guidance

Thanks / Steve

Answer
Discuss

Discussion

Hi Steve and welcome to the Forum. 
Please edit your Question and use the Add Files... button to attach an Excel file (with dummy data if you like). That way we'll better see what you're trying to do. 
John_Ru (rep: 6102) Feb 17, '21 at 11:32 am
Add to Discussion

Answers

0
Selected Answer

Steve

Thanks for adding the file (nearly always helpful).

In the attached version of your file, I've created a pivot table from your data with Part_No for rows and Ship_Date for columns (automaticaaly grouped by Year) and used the Value field in the table values (summed by default).

Then I've added Value again but renamed it from "Sum Of Value2" to "Change from last year" and in the field settings left it to Summarise by Sum but clicked the Show Values As tab and set it to Show values (dropdown)= "%Difference from", Base Value="Years" and Base item= "previous" so the feild return the % increase or decrease from what was sold in the previous year.

Is that what you were hoping to show?

Discuss

Discussion

John:
Many Thanks !!!
I think I see what you did . . . but have to study a bit
Will do when i get done with the garbage from the day job
Thank you so very much !!!
Steve
Slow386 (rep: 2) Feb 17, '21 at 4:28 pm
No problem Steve.
Forgot to say I renamed "Column Labels" and same for rows plus changed the number format for "Change from last year" (to remove default 2 decimal places).
Hope this helps.
John_Ru (rep: 6102) Feb 17, '21 at 4:35 pm
Steve

If you want to remove the #NULL! intries in the "Change from last year" columns, do this:   Right click on any cell in the Pivot Table Click PivotTable options... Layout & Format Format- tick "For error values show:" (and leave box blank or add text like "£0 prev. yr.")

Over to you!
John_Ru (rep: 6102) Feb 18, '21 at 4:39 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login