SOLVED

Percent change pivot table summary

Deleted
Not applicable

Hi all,

 

I am trying to get these % change fields to work properly.  I know this has to be something simple, but can't seem to find an answer anywhere.  The summary line in the pivot is taking a total of the % changes.  Screen shot attached.  Thanks in advance.  

 

 

 

 

3 Replies
best response
Solution
Hi Stephen,

Where is that formula in your screenshot coming from, is typed in those cells or is it a calculated field?

Also, have you used Power Pivot at all in the past? That's the new way to write formula in Pivot Tables (Excel 2013 onwards)

It looks like "% Change" is the field in the source table. With adding to Pivot Table it calculates for the group sum of percentage for the products, not per cent for entire group.

 

To calculate correctly better to add calculated field like =([2018]-[2017])/[2017] to the Pivot Table (if it's not added to data model; if added when the column in Power Pivot). When percentage will be in context and shall show correct result for the group.

Wyn / Sergei, thanks for your responses and time. I was able to identify the issue and update the pivot table to where the subtotal fields reflected the correct %. I had to adjust some of the way the data was being passed from an Access data connect to do it, but it worked. To your point it would have been much easier to do in 2013+ but we are still stuck using 2010 here at work. Have a good rest of the week!
1 best response

Accepted Solutions
best response
Solution
Hi Stephen,

Where is that formula in your screenshot coming from, is typed in those cells or is it a calculated field?

Also, have you used Power Pivot at all in the past? That's the new way to write formula in Pivot Tables (Excel 2013 onwards)

View solution in original post