Hi All,
I've got a pivot table showing sales data from two years (however, the numbers are in the same column in the raw data).
Capture.PNG
Capture2.PNG
I'm trying to create a calculated field where I subtract the 2017 numbers from the 2016 numbers, but I can't figure out how to do it since it's the same field. Essentially, I'm looking to subtract 2016 Sum of Sls Qty US Pound from 2017 Sum of Sls Qty US Pound. Any ideas?
Thanks!
This sounds like a case where you may have to tinker with the source data and add a couple of helper columns. Or you could probably do it with formulas using SUMIFS.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Hi,
What you need is actually a calculated item rather than a calculated field, but it will display the difference for all value fields and not just one of them.
If the data is loaded to the data model, you may be able to achieve the desired outcome using a Set.
Last edited by xlnitwit; 03-20-2017 at 12:23 PM.
Don
Please remember to mark your thread 'Solved' when appropriate.
Your desired results are in a well-defined format, so I think you can use formulas rather than a pivot table to get the results. If you can provide some sample data, it would help a lot.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I've attached a sample of the data and a sample of what I would like the pivot table to turn out as. I know this can be done without a pivot table, however, my client would like for it to be in a pivot table (outside of my control). What I can't figure out how to produce is the Difference and & %Difference columns. I am having trouble because I can't create a calculated field that reads Sales-Sales.
I am open to any suggestions, however, I don't know much about calculated items or sets....willing to learn though!
Well, if your boss absolutely insists on a pivot table, then we need some helper columns in the source data to make this happen. Take a look at what I did in columns F & G. I duplicated the desired out come on Sheet1 to make it easier for me to QA.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks