Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
Something like this?
sum(Revenue)/sum(total <Incumbent_Class> Revenue)
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.
I'll hope this help you.
Cya.
Something like this?
sum(Revenue)/sum(total <Incumbent_Class> Revenue)
Woo Hoo! Worked Great, thanks.
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))
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.
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. |
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.
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% |
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.