5 Replies Latest reply on Feb 3, 2013 3:15 AM by Andrew Marritt

# Difference of change from average change

I have created a graph of difference change from starting time for a series of groups (so every one starts at zero at time=0)

Using a double axis chart I've shown the average change for all groups.

What I'd like to do is show for each group the change - the average change for all groups at that time period.

So on the first sheet the fastest growing group has an 83% change on their starting position over the duration.  The average across groups is 14% change so I'd like to show the fastest group as 69% above the average growth.

• ###### 1. Re: Difference of change from average change

Andrew, did you post the correct version of your workbook?

So on the first sheet the fastest growing group has an 83% change on their starting position over the duration.

Not on the first tab. Here's the % difference from FIRST():

So the fastest growing group has a 26.23% change since the beginning. I do see an 83% change on your sixth tab "Percentage change for each sub group". Is this the tab you want us to work with?

--Shawn

• ###### 2. Re: Difference of change from average change

Shawn,

Apologies, I uploaded an old version by accident.  For some reason Google Drive hadn't synced between Parallels and OSX.

this should have 3 tabs.  The first one is my primary concern but if tab 1 is fixed I should be able to do tab2 using the same calculations.

The first one should look like this:

Thanks for helping

Andrew

Message was edited by: Andrew M to add image

• ###### 3. Re: Difference of change from average change

Pinging Richard.

• ###### 4. Re: Difference of change from average change

You need to calculate the overall percentage difference using the same axis on the dual axis map as the breakdown - which means the same fields on shelves. I've added a calculation that does that using the TOTAL() function, partitioned appropriately. That allows you to subtract the overall from the individual.

Hopefully what I've done makes it clear - if not, ask away - I'll have a look later.

• ###### 5. Re: Difference of change from average change

Thank-you both so much.  If you're in London for ECC this year I'll get you a beer (I used to live in Westminster so know some good pubs).

I was trying to do something with Total but was getting lots of 'can't mix types' sorts of errors.