Sum column based on value of another column, include filter context

[Anonymous]

Hello,

I currently have the following dataset:

Dataset
Agent	Agent (Pair)	Pair Index	Week	Sales	Product
Cory	Cory		11-Feb	1000	A
Angela	Angela		11-Feb	2000	A
Pam	Pam, Jim	1	04-Feb	3000	A
Jim	Jim, Pam	1	11-Feb	4000	A
Michael	Michael, Tony	2	04-Feb	5000	A
Tony	Tony, Michael	2	04-Feb	6000	A
Sam	Sam		11-Feb	7000	C

I want to create a table that

1) filters out Product C,

2) has the paired agents (the second column which is based on their pair index),

3) and the average of the sales of each pair (and if they're not a pair just their own sales). 

Desired Results
Agent (Pair)	Sales
Cory	1000
Angela	2000
Pam, Jim	3500
Jim, Pam	3500
Michael, Tony	5500
Tony, Michael	5500

In this scenario, when I filter on Feb 11, it would take out both Michael and Tony. Since Jim has made $4000 in this week, both "Jim, Pam" and "Pam, Jim" would be in this table with $2000 sales each ($4000/2). 

Desired Results for Feb 11
Agent (Pair)	Sales
Cory	1000
Angela	2000
Pam, Jim	2000
Jim, Pam	2000

How would you go about creating a measure for the sales? I'm currently using a calculated column, but I cannot filter based on Week. 

Thank you so much for your time and I apologize for the complexity. 

[LivioLanzo · Accepted solution]

Hello @Anonymous 

I have uploaded a pbix file for you here: https://1drv.ms/u/s!AiiWkkwHZChHj1_GgTacH8l6Qsxi

Basically, you build the model model:

and then add this measure:

AvgSales = 
    SUMX(
        AgentsPairs,
        CALCULATE( 
            SUM( Sales[Sales] ),
            CROSSFILTER( AgentsPairs[Agent1], Agents[Agent], Both )
        ) * AgentsPairs[Ratio]
    )

Please see the file I uploaded for more info. 

[Anonymous]

Hello, 

Thank you so much! The original request was modified so I had to go a different route, but your solution would have fixed my issue.