Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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 )
)
Your table satisfy all the conditions you explaining.... need more data which clearly differentiate between positive and negative scenarios to construct the Set Analysis