on 06-10-2011 12:52 PM
Hi Experts,
I have a problem in creating a formula. Please find below the information.
Every Opportunity can have more than one Product Id
Opp Id Prod Id Revenue
Oppid1 Prodid1 10
Oppid1 Prodid2 10
Oppid2 Prodid1 15
Oppid3 Prodid1 20
If you see in the above table Revenue for Opp1 is shown as 10+10 =20 where as it should be 10 only(Duplication Error).
I have to calculate Total Revenue :
Revenue
13.75 is shown instead of 45
If I calculate it as ,
Total Revenue = if(Count(product id) in(opportunity id)) >1; Revenue/ Count(product id) in(opportunity id);Revenue) , its giving me 13.75 (10101520)/4 but I want 45(1010)/21520.
Please help me if you have any inputs on this.
Thanks & Regards,
Bhargava Bommidi.
Hi create one variable in name count "=RunningCount(opp ID)In(product id)"
opp id prdid count revenue
op1 p1 1 10
op1 p2 2 10
op2 p1 1 5
op3 p1 1 6
now create one more variable in name CRevenue=If([count]=1)Then[revenue]Else 0
opp id prdid count revenue Crevenue
op1 p1 1 10 10
op1 p2 2 10
op2 p1 1 5 5
op3 p1 1 6 6
total 31 21
this is your expected value
now crevenue total will be 21 instead of 31..........hide the revenue and count column
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Kannan,
Thanks for your inputs.
Now i am able to get 1,2,1,1 but still have some problem calculating the total revenue.
I have to show a single cell which calculates Total Revenue(No need to show oppid,prod id etc in a tabular format)
Now , the problem is when i apply the if logic that you mentioned in the Single Cell,its again giving me the sum as 31 instead of 21..
Any inputs on this will be very helpful to me .
Thanks in advance,
Bhargava Bommidi.
BHARGAVA BOMMIDI ,
calculate a new variable: sum([Revenue] In ([Opp Id])). this should sum the revenues by OppId only.
Thanks,
Karthik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
if u r using an rdbms as the backend db, this looks like a join problem. could u pls explain how ur dim and fact tables are related ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Triying using context
ForEach() Definition.- Add Dimension to the context.
ForAll() Definition.- Remove dimension to the context
Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.