Custom Formula for Percentage Change

[downhillsheep]

Hi and sorry if this has been posted before

I have a spreadsheet that contains a number of items that have percentage distribution for two time periods i.e. the max distribution can only be 100% meaning this particular product is available in all stores

I have put these values into a pivot table and of course, the sub totals sum up all of the values, so I changed the field settings to Max which works perfectly. I then thought to add a custom field that works out the difference between the percentages over the two time periods, but it isn't working as planned as the result is simply telling me the difference between the two summed up sub totals not the max percentages. For example Total A Last Year was 63% and Total A This Year is 83%, percentage change is 32%. Changed totals to Max (Last Year 48%, this year 52%) and the percentage change should be +8%. but is still showing as 32%

Any help would be very much appreciated

[shirleyxls]

Hi and welcome to the forum

It's difficult to help with pivot tables without being able to see the data. Would you be able to upload a small sample (desensitized) so we can take a look?

To attach a workbook, click on the Go Advanced button and scroll down to Manage Attachments. Click there and a new page will open where you can browse to select your file, then click on the Upload button.

[downhillsheep]

Thanks for the welcome

I was afraid that someone would ask for the data, but unfortunately it is a work file and there is two much data.. However, what I maybe able to do is try and recreate the issue with a small amount of data and hope someone can help

[shirleyxls]

That would be brilliant

It doesn't have to be a massive amount... 20 or so rows should be enough. It just needs to reflect the pivot table you currently have and the approach you've already tried.

[downhillsheep]

Ok, can't see the Advanced button and can't manage attachments as the page fails to load

[downhillsheep]

Managed to upload a file, but can't seem to attach

[shirleyxls]

OK thanks - it's there now. I'll try to have a look later for you unless someone else picks it up in the meantime

[shirleyxls]

Hi again

I've had a look at your spreadsheet and the problem is that, although you are displaying the MAX value, when you create a calculated field it is still looking at all the individual data rows in your source, so is basing the calculation on these. If you have a look at Sheet 4, I filtered out just Variant#10Single to prove the calculation that's actually going on in your Field1.

I tried a new calculated field with MAX in front of each of your QTRYA and QTRTY fields, but no joy with that.

The only way I've managed to solve it is to create a new field outside your pivot table which looks at the displayed numbers in each column and performs the calculation. Not ideal I know as it won't grow/shrink with your pivot table, but I haven't been able to come up with an alternative solution.