AnsweredAssumed Answered

Calculating Ratios of Subtotals in Sub-Summary Parts

Question asked by richardsrussell on Jul 24, 2013
Latest reply on Jun 23, 2014 by d24601

Please indulge me as I work my way up to my problem by reviewing the various things I've tried that didn't work.

 

To illustrate the problem, I've ginned up a little toy database that boils it down to its essence by using a super-simple demo example of a school PTA trying to raise money by doing candy sales. We know how many kids there are in each classroom and how much money each class raised, and we want to do a simple analysis involving 2 calculated ratios:

• this year's sales per kid (a dollar result)

• this year's sales compared to last year's for the same classroom (a percent result)

 

Here's what we start out with ("Preliminary" layout):

 

Preliminary.png

 

(Note the particularly sterling performance by Ms. Clark's 3rd graders, perhaps due to little Billy Gates transferring in this year.)

 

The "Totals" are just that, summary-field totals of the detailed numbers in the column above. Obviously the 2 pink values are meaningless, since the total of an average is virtually never the same as the average of the totals, which is what we're really interested in. (I didn't actually try this, I just threw it in the demo file for completeness.)

 

So what will work? Next I tried using a different kind of summary field, the average. You see the results in blue — the average of each column per record (for example, $1,004,800 in sales divided by 5 classes = $200,960 per class). But we didn't want "per class", we wanted "per kid". Onward!

 

Next I tried to see if a weighted average would work. I calculated the summary-field average of "Sales2013" weighted by "Kids" for Column E and by "Sales2012" for Column G. But no, this took the numbers in the wrong direction, as shown in green. Weighting evidently applies to the numerator, not the denominator.

 

So that led to trying something other than summary fields, namely a calculation that used summary fields as its components. The formulas were:

• Ratio per Kid = ∑ Sales2013 / ∑ Kids

• Ratio per PrevYr = ∑ Sales2013 / ∑ Sales2012

And this seemed to produce the desired results, as indicated by the purple fields above. $1,004,800 in sales divided by 128 kids really is $7,850 per kid, and divided by $7,000 the prior year really is a 14,354% increase.

 

So I duplicated the layout, called it the "Final" layout, and modified it in order to concentrate on just the parts that seemed to work:

 

Final.png

 

(Aside: Notice that the purple background came over just fine for the ratios.) Not done yet, tho, because what we're looking at here is all 5 records in the file. Would those calculations still work for a subset of them? For example, for all the female teachers?

 

Females Only.png

 

Or all male teachers?

 

Males Only.png

 

And the answers were "yes" and "yes". So this looked like success (aside from the purple background to the ratio fields having mysteriously vanished).

 

But now we come to the gory bit, and this is where I get to point out my problem before throwing myself on the mercy of the assembled masses. Would that same trick work within a sub-summary field? That is, if I sorted all 5 records by sex, could I get subtotals showing $1,002,700 for women teachers and $2,100 for men teachers, on the same report at the same time? Let's look:

 

Subtotals.png

 

Answer: NOOOO! Each of the numbers that's supposed to be a ratio of subtotals is actually just the ratio of the grand totals.

 

Thus my problem, complaint, and query: How can I show an accurate ratio of 2 subtotals within a sub-summary part?

 

I am attaching my toy file in case you want to play around with it. I ginned it up using FMPA 12.0v4 running under Mac OS X 10.8.4.

 

Also, quite as an aside, and in no way critical to my main question, if you have any idea where the purple background on my ratios went, that's irritating me, too. I assure you it's still there in Layout mode.

Outcomes