Help with Dividing when 2 or more roles

[apvu2]

So I have two data sets one is my master table, table X, which contains a client name and a daily revenue.  then have another table, table Y that contains salesmen who worked on this client. I have a reationship netween the two tables Liked by Client. I also have a matrx tabe set up to see the revenue, and  I am then using splicers to splice the Sum of revenue to see which role generated what revenue, etc. What I am trying to do is when lets say for "Target" when I splice by "Team Lead" the revenue should be divided by 2 as there is 2 team leads so the profit splits in half. or If I splice it by sales, since Target only has one salesmen revenue would go 100% to Chris, where as Wendys has 3 so revnue would be split into 3, or 33% each. it would divide by only 1 since there is only 1 sales men. It does not matter about the role as long as there is 2 the revnue would be split, but if it is only one it doe Not apply. 

Table X

Date	Client	Daily Revenue
12/2/2021	Big Lots	100
12/3/2021	Big Lots	100
12/4/2021	Big Lots	100
12/5/2021	Big Lots	100
12/6/2021	Big Lots	100
12/7/2021	Big Lots	100
12/8/2021	Big Lots	100
12/9/2021	Big Lots	100
12/10/2021	Big Lots	100
12/11/2021	Big Lots	100
12/12/2021	Big Lots	100
12/5/2021	Target	50
12/6/2021	Target	50
12/7/2021	Target	50
12/8/2021	Target	50
12/9/2021	Target	50
12/4/2021	Wendys	200
12/5/2021	Wendys	200
12/6/2021	Wendys	200
12/7/2021	Wendys	200

Table y

Jeff	Team Lead	Target
Matt	Team Lead	Target
Chris	Sales	Target
Jen	Team Lead	Big Lots
Kim	Sales	Big Lots
Jeff	Sales	Wendys
Chris	Sales	Wendys
Kim	Sales	Wendys

[ryan_mayu · Accepted solution]

@apvu2 

is this what you want?

Column = SUMX(FILTER('Table X','Table X'[Client]='Table Y'[Client]),'Table X'[Daily Revenue])/CALCULATE(COUNTROWS('Table Y'),ALLEXCEPT('Table Y','Table Y'[Client],'Table Y'[Role]))

[apvu2]

Yes thank you!

[ryan_mayu · Accepted solution]

@apvu2 

is this what you want?

Column = SUMX(FILTER('Table X','Table X'[Client]='Table Y'[Client]),'Table X'[Daily Revenue])/CALCULATE(COUNTROWS('Table Y'),ALLEXCEPT('Table Y','Table Y'[Client],'Table Y'[Role]))

[apvu2]

Thank you!

[ryan_mayu · Accepted solution]

you are welcome