yo_bee
asked on
How to calculate Difference for two year in a Pivot Table using a Forumlas
I have a Pivot table that has the rows as users Columns label is Date (Year) and Sum of Values,
Values are sum per user for Bills and Sum per user Paid.
I need to calculate the difference between 2012 - 2013 paid as well as 2012 - 2013 Billed.
I see there is a Difference From option as well as using formulas.
Should I just do the calculation outside the PivotTable?
Values are sum per user for Bills and Sum per user Paid.
I need to calculate the difference between 2012 - 2013 paid as well as 2012 - 2013 Billed.
I see there is a Difference From option as well as using formulas.
Should I just do the calculation outside the PivotTable?
if you use some column outside of the pivot with formulas and references to the Pivot table cells in these formulas then you should calculate it without any problem
ASKER
All columns being references are within the PivotTable.
do you mean like this? (attached sample)
Book1.xlsx
Book1.xlsx
ASKER
Yes
ASKER
Note: My dates are Grouped by Year, Month
OK, but in your screen I see only years - if you need to apply specificely to you your case, please attach your data (or sample) to check
ASKER
attached is the file
Book1.xlsx
Book1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi, so now I extracted year from your dates and created pivot based on year
Check attachments. (2 versions attached)
Copy-of-Book1.xlsx
Copy-of-Book2.xlsx
Check attachments. (2 versions attached)
Copy-of-Book1.xlsx
Copy-of-Book2.xlsx
ASKER
@HelpFinder:
That is what I was hoping to avoid. I want to use the Formula tool to create a field within the pivot table.
That is what I was hoping to avoid. I want to use the Formula tool to create a field within the pivot table.
ASKER
I was not able to get to the final result from your solution, but it pointed me in the right direction.
Found what I was looking for.
I needed to Drag the Fees Bill Field and change it from sum to show value as, Selected the option Difference From and where I was going wrong before was choosing my base field
I just need to select Years (from the grouping) then Previous and it worked.
Thanks for the effort guys, but
Found what I was looking for.
I needed to Drag the Fees Bill Field and change it from sum to show value as, Selected the option Difference From and where I was going wrong before was choosing my base field
I just need to select Years (from the grouping) then Previous and it worked.
Thanks for the effort guys, but