dax for calculating weighted share of total

[ggww]

Hi ,

I  am working on a solution but am really stuck, any help will be much appreciated:

Sales are made by teams, each team is consist of one or more members, one person can be in more than one team.

if there is only one member then obviously that person claims 100% credit of the sales, however if there are two team members then normally their share are split equal but it can also be different such as one claims 30% another 70% etc.

for team of three it can be 30%+30% + 40% etc.

I want to be able to report on individuals level for their share of sales, any suggestions writting the dax?

sales:

team date amount

A  2018-01-02   $2000

B  2018-02-03   $1500

C 2018-06-06    $3000

Team:

Team / Member / Share

A - Peter *  50%

A - Sally     50%

B - Peter *   30%

B - Claire     70%

C - Alice     100%

employee:

Peter

Sally

Claire

Alice

(the two Peters are the same person)

[PattemManohar]

Could you please explain, what’s the criteria for splitting Team A into 50% and 50% but for Team B it is 30% and 70%. Both teams has two members.

Also, what is your expected output ?

[ggww]

Hi Pattemmanohar,

the split is given and stored in the table, not automatic determined.

the outcome expected will be like this:

employee / sales

Peter   1450 (50% x 2000 + 30%x1500)

Sally    1000  (50% x 2000)

Claire   1050  (70% x 1500)

Alice     3000 ( 100% x 3000) 

thanks

[affan · Accepted solution]

Hi @ggww,

Try this:

Add a calculated column to your 'Teams' table as below.

MemberShare = 
var Tsale=LOOKUPVALUE(Sales[Amount],Sales[Team],Teams[Team])
var teamsale=CALCULATE(Tsale,FILTER(Teams,Teams[Member]=EARLIER(Teams[Member])),Filter(Teams,Teams[Team]=EARLIER(Teams[Team])))
var share=CALCULATE(SUM(Teams[Share]),FILTER(Teams,Teams[Member]=EARLIER(Teams[Member])),Filter(Teams,Teams[Team]=EARLIER(Teams[Team])))

return teamsale*share

I have got the following result 

You can also  download the pbix file for your reference.

https://www.dropbox.com/s/abb89ud6i0fiydy/calculating%20weighted%20share%20of%20total.pbix?dl=0

Please mark this post as an accepted solution if this helped you.

Regards,

Affan

[ggww]

I have at the end worked out another solution as shown above.