7 Replies Latest reply on May 16, 2018 7:41 AM by Olalekan Agboluaje

# % difference in Grand total Calculation

Hi All,

The % difference in my Grant total view is not working correctly The % difference is calculated by (Year 2 sales / Year 1 sale) -1, but for the Grand total, i expect it to do the same calculation but the best i can get it to do is an average which is wrong.

Basically, i expect the sales movement for the grand total to be calculated as ( 117663 / 126946) - 1

Thanks for your help

Ola

• ###### 1. Re: % difference in Grand total Calculation

Keep your Cursor at that point in Grand Total and from Menu Change from Automatic to Average or whatever u need.

• ###### 2. Re: % difference in Grand total Calculation

Hi Deepak,

That does not solve the problem. That only has the option of a SUM or AVERAGE.

i want the calculation to be the % difference of Year1 and Year2.

Thanks

Ola

• ###### 3. Re: % difference in Grand total Calculation

Hi Olalekan,

Please could you let me know whether your % difference is calculated by using the aggregated sum of Year 1 and sum of year 2? If it's not, then that would be the problem, you need it to do the sum of each year such that the grand total would work out the percent change of 117663 and 126946.

Thanks,

Mavis

• ###### 4. Re: % difference in Grand total Calculation

Hi Olalekan.

Please see the attached workbook where the first worksheet uses a a calculation which is only aggregated AFTER the calculation has been processed, and the second worksheet uses a calculation which is aggregated beforehand. You can see the difference here: Where the non aggregated worksheet gives a correct answer on a row by row level, but not at a grand total level. However the aggregated version gives the correct for both the row level and grand total.

Thanks,

Mavis

• ###### 5. Re: % difference in Grand total Calculation

Hi Mavis,

Unfortunately, i could not open your attached workbook. can you resend it please.

Also note that the calculation works well, its the Grand total for % difference that is the issue

The Year1 and year Year2 are calculated fields and the % difference is calculated as well.  so the % difference is calculated as (Year2 / Year1) -1

Thanks

Ola

• ###### 6. Re: % difference in Grand total Calculation

Hi Ola,

I've attached my previous workbook, but my example is different to yours because now I can see you're already dividing by countd of issue date.

Please could you try this calculation instead when working out the difference?

(sum({fixed [dimension which is in blue in your original screenshot] : (Year 2 Sales (copy 2))})/sum({fixed [dimension which is in blue in your original screenshot] : (Year 1 Sales (copy 2))}))-1

See whether this makes a difference at all...

Thanks,

Mavis

1 of 1 people found this helpful
• ###### 7. Re: % difference in Grand total Calculation

Hi Mavis,

That worked,

Thanks a bunch

Ola