2 Replies Latest reply on Apr 21, 2016 3:55 PM by Gene Denny

# Creating a Sub-Total that is DIFFERENCE and not SUM

So, I've posted on this topic before and, since then, have reshaped my data in an effort to get this to work.

Take the attached packaged workbook.

I would like to add a 3rd column to the first pane that reflects the DIFFERENCE between column 1 and column 2.  In other words, for Brody Middle School, I want a third column that is 66.00% - 60.00% or 6.00%.  I believe this is a LOD scenario but, once again, I'm completely stuck.  Any of you masters able to lend a hand?

Here's what my source data looks like (SQL) for Brody: • ###### 1. Re: Creating a Sub-Total that is DIFFERENCE and not SUM

Gene,

Is this what you are looking for?

I used the instructions on the below link to create the difference subtotal:

The formula for the difference calculations looks like this:

IF FIRST() = LAST() THEN

SUM([ELA Percent A's & B's])

ELSE IIF(FIRST()==0,

( LOOKUP( SUM( [ELA Percent A's & B's] ) , LAST() ) - LOOKUP( SUM( [ELA Percent A's & B's] ) , FIRST() ) ) , NULL )

END

I also turned on subtotals, instead of the grand totals as the above link shows, and computed both calculations with Endyear (copy).

It may be a bit complicated, so let me know if you need any clarification.

Cheers!

1 of 1 people found this helpful
• ###### 2. Re: Creating a Sub-Total that is DIFFERENCE and not SUM

Joshua, this is EXACTLY what I've been striving for and I sincerely appreciate not only your solution, but the full explanation and link as well!

Thank you SO much!