Pivot Table % Change -- Is it possible?

[melfricke]

I watched the excel videos 610-611 and I was hoping to find a solution for % change, after the reading the comments I found that you had suggested posting questions here. I searched but didn't find a match.

When calculating the "% Of" in the pivot table, I find that I'm not actually calculating the % change which is the same formula minus 1. An example:

% OF
=current year/previous year

% Change
=current year/previous year-1

I would like to calculate an actual % change and hoped that there was a way to do that within a pivot table.

Thanks.

 

[DILIPandey]

Yes melfricke,

You can do this in pivot table using calculated field.

By BODMAS rule below formula should work:-

=cy/py-1

and it's working in my sample file

Regards,
DILIPandey

 

[melfricke]

So I took a look and I'm not seeing that this is possible in a pivot table. When I go to the Value Field Settings > show values as, the drop down menu lists formulas but this particular one is not there. The formula for % change is not listed. I understand the formula will work elsewhere, particularly out side of a pivot table, but please show me how you made this work in your sample file.

Thank you!

Yes melfricke,

You can do this in pivot table using calculated field.

By BODMAS rule below formula should work:-

=cy/py-1

and it's working in my sample file

Regards,
DILIPandey

 

[V_Malkoti]

Assuming that you already have two columns CurrentYear and PreviousYear in your pivot, you probably need to create a calculated field.

Click on your pivot table and you should see two new ribbon menus appear - pivottable options and design. In Options you should see an option "Fields, Items & Sets". Click on it and select "Calculated Field". In the dialog box that appears you can write formula, assign it a name and use it in your pivottable.

 

[melfricke]

I don't have those columns? It wouldn't work with my data organization, in fact, I'm not sure that it would make sense to organize data like that ever.

I use Calculated field often, but in this case I can't spell out how to identify current period or previous period. THese would all be in one column with a new line items for each period. Our goal is to compare the previous quarter and previous year with the current quarter.

This isn't the solution. I need to get a % Change for impressions, clicks, spend... ect.

Assuming that you already have two columns CurrentYear and PreviousYear in your pivot, you probably need to create a calculated field.

Click on your pivot table and you should see two new ribbon menus appear - pivottable options and design. In Options you should see an option "Fields, Items & Sets". Click on it and select "Calculated Field". In the dialog box that appears you can write formula, assign it a name and use it in your pivottable.

 

[DILIPandey]

okay melfricke, post the small sample of your data ?


Regards,
DILIPandey

 

[melfricke]

I can use grouping for the date range but I need to bundle it by Quarter/Year. Since we're seeking to compare the previous quarter and the previous year with the current quarter.

[TABLE="width: 2908"]
<colgroup><col><col><col span="4"><col><col span="5"><col span="2"></colgroup><tbody>[TR]
[TD]Quarter[/TD]
[TD]Year[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Ad Group[/TD]
[TD]Network[/TD]
[TD]Campaign Name[/TD]
[TD]Converted Clicks[/TD]
[TD]performance_indicator[/TD]
[TD]Impressions[/TD]
[TD]Clicks[/TD]
[TD]Cost[/TD]
[TD]Frequency[/TD]
[TD]Reach[/TD]
[/TR]
[TR]
[TD]Q3 2015[/TD]
[TD][/TD]
[TD]2015-12-01[/TD]
[TD]2014-09-30[/TD]
[TD]A[/TD]
[TD]News Feed on Desktop Computers[/TD]
[TD]GreenT[/TD]
[TD]11[/TD]
[TD]actionsffsite_conversion[/TD]
[TD]717[/TD]
[TD]21[/TD]
[TD]7.23[/TD]
[TD]1.03913[/TD]
[TD]690[/TD]
[/TR]
[TR]
[TD]Q3 2014[/TD]
[TD][/TD]
[TD]2014-09-01[/TD]
[TD]2014-09-31[/TD]
[TD]B[/TD]
[TD]News Feed on Desktop Computers[/TD]
[TD]GreenT[/TD]
[TD]13[/TD]
[TD]actionsffsite_conversion[/TD]
[TD]450[/TD]
[TD]43[/TD]
[TD]5.23[/TD]
[TD]1.236264[/TD]
[TD]364[/TD]
[/TR]
[TR]
[TD]Q2 2014[/TD]
[TD][/TD]
[TD]2014-06-01[/TD]
[TD]2014-06-30[/TD]
[TD]C[/TD]
[TD]News Feed on Desktop Computers[/TD]
[TD]GreenT[/TD]
[TD]7[/TD]
[TD]actionsffsite_conversion[/TD]
[TD]2682[/TD]
[TD]35[/TD]
[TD]24.87[/TD]
[TD]1.045207[/TD]
[TD]2566[/TD]
[/TR]
</tbody>[/TABLE]

okay melfricke, post the small sample of your data ?


Regards,
DILIPandey

 

[DILIPandey]

Hi melfricke,

Based on your sample data, I have created below pivot table:-

[TABLE="width: 174"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sum of Reach[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]690[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]2930[/TD]
[/TR]
[TR]
[TD]% chg[/TD]
[TD]-76%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]3619.235495[/TD]
[/TR]
</tbody>[/TABLE]

where % chg is a calculated item having formula as :-

='2015' /'2014' -1

I can see the % change in output. Thanks.

Regards,
DILIPandey

 

[melfricke]

Ahh Yes!

That does work. I've never used calculated items... Now to find a way to make it so I don't have to update the formula every quarter. This is a good start.

Hi melfricke,

Based on your sample data, I have created below pivot table:-

[TABLE="width: 174"]
<tbody>[TR]
[TD]Sum of Reach[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]690[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]2930[/TD]
[/TR]
[TR]
[TD]% chg[/TD]
[TD]-76%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]3619.235495[/TD]
[/TR]
</tbody>[/TABLE]

where % chg is a calculated item having formula as :-

='2015' /'2014' -1

I can see the % change in output. Thanks.

Regards,
DILIPandey

 

[DILIPandey]

Glad to help...!!

Cheers:

Regards,
DILIPandey