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