Hello,
First time poster. I’m a self-taught BO user over the past 12 years. I’m having a rather unique issue that is always throwing a wrench in my reports so I’m finally reaching out. Sorry for the long post, but I’m trying to be precise here.
I have two universes I frequently use that that I merge on client account number (the shared dimension). One universe (U1) contains each account’s value (a measure) and the other (U2) contains each account’s revenue (a measure). I do not have the ability to modify these universes’ structures - it is what it is.
Within U1 each account is assigned a “lead” account number (another dimension). This “lead” account serves to tie multiple accounts together into a single relationship. I use the “In” context operator to calculate the relationship value of each relationship.
Relationship Value =Sum([Account Value) In ([Lead Account])
Pretty simple stuff so far. The report output would look like this:
Acct# Lead Acct Account Value Relationship Value
123 789 25,000 43,000
456 789 10,000 43,000
789 789 8,000 43,000
ABC DEF 3,000 18,000
DEF DEF 15,000 18,000
GHI GHI 5,000 5,000
Management frequently wants me to show relationships values within various tiers – i.e. “What is the aggregate value of a portfolio manager’s relationships valued < $25,000” “How many relationships does a PM have valued between 25,000 and 45,000” so on and so forth.
I use “where” to isolate these values:
Value of Relationships < $25,000 = sum([Account Value] foreach([Acct#]) Where ([Relationship Value] <25000)) in ([Lead Acct])
Resulting in a report that shows the following using the data above:
Acct# Lead Acct Relationships < 25,000
123 789
456 789
789 789
ABC DEF 18,000
DEF DEF 18,000
GHI GHI 5,000
My issue arises when I merge on Acct# with U2 to try to repeat the same tier breakdowns using the revenue value for each account from that universe. Even though I’m merged on the acct# the calculations I used for account value will not work on the revenue measure.
Relationship Revenue = sum([Account Revenue]) in ([Lead Acct])
Acct# Lead Acct Account Value Relationship Value Account Revenue Relationship Revenue
123 789 25,000 43,000 1,000 1,000
456 789 10,000 43,000 500 500
789 789 8,000 43,000 450 450
ABC DEF 3,000 18,000 300 300
DEF DEF 15,000 18,000 800 800
GHI GHI 5,000 5,000 700 700
As you can see in the table above, the Relationship Revenue formulas just repeats the individual Account Revenue value instead of aggregating at the Lead Acct level. Adding a forcemerge() around Account Revenue seems to do the trick for the report above:
Relationship Revenue = sum(ForceMerge([Account Revenue])) in ([Lead Acct])
Acct# Lead Acct Relationship Revenue
123 789 1,950
456 789 1,950
789 789 1,950
ABC DEF 1,100
DEF DEF 1,100
GHI GHI 700
However, when I attempt to break out by tiers I’m getting results again at the individual account level, even using force merge.
Revenue of Relationships < $25,000 = sum(ForceMerge([Account Revenue]) foreach([Acct#]) Where ([Relationship Value] <25000)) In ([Lead Acct])
Acct# Lead Acct Relationships < 25,000 Revenue of Relationships < 25,000
123 789 1,950
456 789 1,950
789 789 1,950
ABC DEF 18,000 1,100
DEF DEF 18,000 1,100
GHI GHI 5,000 5,000
It’s completely ignoring my “Where” even though the syntax is exactly the same as the value. Why would this be?