9 Replies Latest reply: Jul 11, 2011 2:42 PM by John Witherspoon

# Subtotal of subtotal....with a twist

Gang - need your expertise on this issue. I need to have each of my "Incumbent Class" segments add up to 100%. As of now, the math divides it up accross the entire field - which i dont want.

'New Work', 'Retain', and 'Share Growth' are all text attributes within my data. Since this is text within the field, i'm having trouble pulling out this calculation as it relates to the example below.

What do you recomment?

Current State

 Incumbent_Class New Work New Work Retain Retain Share Growth Share Growth Total Total Result Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total WON \$13,565,943. - \$9,888,327. - \$9,656,199. - \$10,323,343. - LOST \$5,569,704. - \$5,545,557. - \$8,139,290. - \$7,256,197. - Total \$9,945,184. - \$8,259,788. - \$9,002,761. - \$9,026,600. -

Desired State

 Incumbent_Class "New Work" "New Work" "Retain" "Retain" "Share Growth" "Share Growth" Total Total Result Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total WON \$13,565,943. 71% \$9,888,327. 64% \$9,656,199. 54% \$10,323,343. 59% LOST \$5,569,704. 29% \$5,545,557. 36% \$8,139,290. 46% \$7,256,197. 41% Total \$19,135,647. 100% \$15,433,883. 100% \$17,795,489. 100% \$17,579,540. 100%

• ###### Re: Subtotal of subtotal....with a twist

Hi, recently i experienced something like that.

What i did is:

Create a variable like TableName.TotalAnnulizedRev = Sum(TableName.AnnulizedRev)

Then in the chart the expression should be: Sum(TableName.AnnulizedRev) / TableName.TotalAnnulizedRev

And them configure this expression to show data in percentage style.

Cya.

• ###### Re: Subtotal of subtotal....with a twist

Something like this?

sum(Revenue)/sum(total <Incumbent_Class> Revenue)

• ###### Re: Subtotal of subtotal....with a twist

Woo Hoo! Worked Great, thanks.

• ###### Re: Subtotal of subtotal....with a twist

John, one last question and this will close it out. For the "Annualized Revenue," this seems to be throwing me off. Basically I want to take the months of the contract and divide that over the total revenue. In short, "Eval Revenue / Duration (months)" using these Incumbent Classes. Here's what i have...the totals dont add up....wierd.

 Incumbent_Class New Work New Work Retain Retain Share Growth Share Growth Total Total Result Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total WON ####### 21% \$9,888,327. 17% \$9,656,199. 12% \$10,323,343. 14% PENDING \$6,907,328. 72% \$7,992,175. 77% \$7,967,419. 81% \$7,813,350. 79% LOST \$5,569,704. 7% \$5,545,557. 6% \$8,139,290. 7% \$7,256,197. 7% Total \$7,557,634. 100% \$8,051,235. 100% \$8,145,808. 100% \$8,034,882. 100%

=SUM([Eval Revenue]), SUM([Eval Revenue])/div(SUM([Duration (months)]),12))

• ###### Re: Subtotal of subtotal....with a twist

I don't really follow, but I suspect you want something more like this:

12*sum([Eval Revenue]/[Duration (months)])

Because (A+B)/(C+D) =/= A/C + B/D, and I think you want the latter.

• ###### Re: Subtotal of subtotal....with a twist

Close, let me to a better job of framing it up....

Annualized is just the value of the sale / contract years. For example a \$12,000 sale over 2 years will have an annualized value of \$6,000. My trouble is with the incument class again. How can i pull through only the sales that are categorized like the table below in an annualized format? PS: The 'totals' dont jive either.

Thanks John!

 Incumbent_Class New Work Retain Share Growth Total Result Annualized Rev Annualized Rev Annualized Rev Annualized Rev WON \$13,565,943. \$9,888,327. \$9,656,199. \$10,323,343. PENDING \$6,907,328. \$7,992,175. \$7,967,419. \$7,813,350. LOST \$5,569,704. \$5,545,557. \$8,139,290. \$7,256,197. Total \$7,557,634. \$8,051,235. \$8,145,808. \$8,034,882.
• ###### Re: Subtotal of subtotal....with a twist

I still don't follow, and to the extent that I do, my expression still looks right to me.  Taking your example, you have a \$12,000 sale over 2 years.  2 years is 24 months, so I assume field [Duration (months)] has 24 in it.  And therefore, if this is the only sale, my expression gives you 12 * \$12,000 / 24 = \$6,000 as you requested.  What I was trying to say with my post is what do you do if you have a second sale, this one for \$6,000 over 3 years?  That one sale is annualized to \$2,000.  So is the total supposed to be \$8,000 annualized?  If so, then the expression I gave gives that number, 12 * (12000/24 + 6000/36) = 8000.  If you want this split out by incumbent class, and incumbent class is a dimension, then it should split it by incumbent class.

I can't make any sense of your table without underlying data, particularly when you tell me that the totals aren't right.  It's just random numbers to me.

• ###### Re: Subtotal of subtotal....with a twist

Hopefully this helps - here's an example of the raw data and the revised table.

Regards,

Brian

 Results Incumbent Class Value Annualized Duration (Month) WON New Work \$       954 \$          954 12 LOST Retain \$       985 \$          493 24 WON New Work \$     1,000 \$          200 60 LOST Retain \$       400 \$          200 24 WON Retain \$       332 \$          166 24 WON New Work \$       100 \$          100 12 LOST Share Growth \$       343 \$            86 48 WON Share Growth \$       234 \$            78 36 LOST New Work \$       343 \$            69 60 WON Share Growth \$         45 \$            45 12

 Incumbent_Class New Work New Work Retain Retain Share Growth Share Growth Total Total Result Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total Annualized Rev % of Total WON \$1,254. 95% \$166. 19% \$123. 59% \$1,543. 65% LOST \$69. 5% \$693. 81% \$86. 41% \$848. 35% Total \$1,323. 100% \$859. 100% \$209. 100% \$2,391. 100%
• ###### Re: Subtotal of subtotal....with a twist

That helped a lot.  Here you go:

Annualized Rev = 12*sum(Value/"Duration (Month)")
% of total = "Annualized Rev"/(12*sum(total <"Incumbent Class"> Value/"Duration (Month)"))

See attached.