Key Figure Duplication Problem in Webi Report

[Former Member]

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.

[Former Member]

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

[Former Member]

Hi Kannan,

"=RunningCount(opp ID)In(product id)"

This formulae is giving the values as 1,2,3,4 not 1,2,1,1.

[Former Member]

Hi,

share the exact formula that you have written because ......I have tested that formula my side and its giving perfectly 1 2 11

[Former Member]

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.

[Former Member]

hi,

its very simple ......drag and drob single cell and inside that cell write formula like =sum(Amount-variable)

[Former Member]

Hi Kannan,

If i use sum(Revenue Variable) in the Single cell, It is giving me 31 instead of 21.

Do i need to apply any other restrictions.

Can you please help me in this regard.

Thanks & Regards,

Bhargava Bommidi

[Former Member]

Ya...

Am also trying to solve this ......table fold is not available in webi...to show only total....we will try to do some work for achive this

[Former Member]

hi its not possible.....better try to this in unierse level........

[Former Member]

BHARGAVA BOMMIDI ,

calculate a new variable: sum([Revenue] In ([Opp Id])). this should sum the revenues by OppId only.

Thanks,

Karthik

[arijit_das]

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 ?

[Former Member]

HI ,

I am using MS SQL Server 2008 as the database.

Opp id and Revenue are from Opp Table

Product id is from Product table.

Can you please let me know what shud be the join type/Cardinality for my requirement.

Thanks in advance.

Bhargava Bommidi

[Former Member]

Hi,

Triying using context

ForEach() Definition.- Add Dimension to the context.

ForAll() Definition.- Remove dimension to the context

Regards