Set Analysis - To avoid double counting

[Anonymous]

Here is a sample table (Attached)

Op_ID	Op_Name	Op_Lead	Team_Member	Team_Role	Emp_ID	Sales
1	ABC	John	Alan	Lead	A100	10
1	ABC	John	Abhay	Support	A200	10
1	ABC	John	Sam	Support	A300	10
1	ABC	John	John	Support	A500	10
1	ABC	John	John	Lead	A500	10
2	DEF	Ron	Kyle	Support	A400	20
2	DEF	Ron	Russell	Support	A600	20

Each opportunity has a Op_Lead and multiple team members.

What I want:

To Sum Sales of Opportunity Records if a person is Op_Lead OR is listed as a Lead under "Team_Role"

For example in Op_ID 1 we should get $10 Sales amount for John, because he is list as a lead under Team_Role and his name is also listed as Op_Lead.

Similarly for Op_ID 2, we should get $20 for Ron as he is listed as a Op_Lead,  even though he is not listed under Team_Roles

Wondering how to achieve this ? Any help is appreciated. Thanks

[neilhepburn]

I suggest you remodel your data into three (3) tables: Opportunities; Staff; Staff Opportunity Role.  Using your data, the tables would be modeled as:

Opportunities:

Op_ID,Op_Name,Sales

1,ABC,10

2,DEF,20

Staff:

Emp_ID, Staff Name

A100,Alan

A200,Abhay

A300,Sam

A400,Kyle

A500,John

A600,Russell

AXXX,Ron

[Staff Opportunity Role]:

Emp_ID,Op_ID,Role

1,A100,Lead

1,A100,Lead

1,A200,Support

1,A300,Support

1,A500,Support

1,A500,Lead

2,A400,Support

2,A600,Support

2,AXX,Lead

Please note: Due to the way the data would be loaded, John is duplicated in the bridge table as Lead.  However, this will not make any difference to totals assuming you are only using the [Staff Opportunity Role] table as strictly a bridge table. But if this is an issue, the [Staff Opportunity Role] table can be easily de-duplicated during load time.

[vkish16161]

Try this:

Sum (Total <Op_Lead,Op_ID> distinct Sales)    // Considering Op_Lead,Op_ID are chart dimensions

or

Sum

(

Aggr (

Sum(distinct Sales)

,

Op_Lead , Op_ID )

)

[jayanttibhe]

Your table satisfy all the conditions you explaining.... need more data which clearly differentiate between positive and negative scenarios to construct the Set Analysis