8 Replies Latest reply on Aug 21, 2018 10:27 AM by Dave Attrill

# Grand Totals calculation not working as desired

Hello everyone, I have a simple dataset showing sales and margin dollars and the percentage between the two.. however my total line is summing the percentages from the percentage column, but I need it to do a calculation based on the YTD Sales \$ vs YTD Margin \$ to give me a true representation of the overall margin percentage, ie 84.64/471 = 17.97% any tricks to doing this?  FYI I still do need the total column to show the dollar totals for the YTD Sales and YTD Margin so that part is working as desired.

• ###### 1. Re: Grand Totals calculation not working as desired

Hi Dave

see the attached

I changed the calc 1 and 2

and it now returns this

jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Grand Totals calculation not working as desired

ok so that worked in the simple example I provided you unfortunately in my true data it still didnt fix it.. I have uploaded a new copy of my dataset workbook where it still doesnt work for the "Budget Margin %" total.. its calculating to 30.91%, but 652.8/2048 should actually be 31.875%  Any thoughts on whats going on here?

• ###### 3. Re: Grand Totals calculation not working as desired

I guess I am missing something

this is from the book I sent you -

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Grand Totals calculation not working as desired

ok, yes something very strange, this is how it appears in my program, is there some setting somehow that could do this sort of thing?  I also opened the file I uploaded here and its still giving me the wrong %

• ###### 5. Re: Grand Totals calculation not working as desired

here is another copy - same file just a new copy

make sure that all % calculations are set to Total Automatic

jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 6. Re: Grand Totals calculation not working as desired

Hi Jim, thank you so far with all your help, I see the sample that you provided does indeed work as expected.  Unfortunately the original format for the workbook I am working on still isnt working as intended even with your adjustments to the calculations.  Please take a look at the newly uploaded teste.twbx  it has virtually the same data, and is giving me the same bad result of 30.91%

again thx for your help, I am pulling my hair out for something that should be straight forward.

• ###### 7. Re: Grand Totals calculation not working as desired

Good morning

this is your first book you had a value that was the Max cy budget or sales value)

and you used

this is you latest book you took the max of a number called budget or sales ...

not the sum

to make it like the first i created 3 new calculations

and

and it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 8. Re: Grand Totals calculation not working as desired

Thank you this all makes sense, I realized too just as you responded that my original 'Budget Margin %' was not filtering enough of the rows of data to give the intended result.  I see how you qualify it now based on your example.  thank you