Commission Calculation - A Shared Deal between 2 sales people

[YavuzDuran]

Hi All,

I have a sales team, and some sales are being shared between 2 salespeople, for commission. Each has a % share.

The formula for Commission is :

=SUMIF($B$2:$B$7,B12,$G$2:$G$7)+SUMIF($D$2:$D$7,B12,$H$2:$H$7)

On Power BI Sales Person 1 is my Sales Person name which I put on my table to calculate the total commission for each. 

A Sales Person name appears most of the time in the Salesperson 1 column but also on Sales Person 2 column for some deals.

I tried to find all lines a salesperson name appears (either on Salesperson name 1 or salesperson name 2) and used this formula 

Total Commission = sum(SalesCommission[Sales Person 1 Commission])+calculate(sum(SalesCommission[Sales Person 2 Commission),

filter(SalesCommission,CONTAINSSTRING(SalesCommission[Sales Person 2],SalesCommission[Sales Person 1])))

but it is not working, 

All I am trying to find those lines in which Sales Person 2 column contains Sales Person 1 name and return the Sales Person 2 Commission number. 

Please help, Thank you

[v-yalanwu-msft · Accepted solution]

Hi, @YavuzDuran ;

You could create a measure as follows:

Measure = 
var _a=CALCULATE(SUM([Sales Person 1Sales Person 1 Commission]),ALLEXCEPT('Table','Table'[Person1]))
var _b= CALCULATE(SUM([Sales Person 2 Commission]),FILTER(ALL('Table'),[Person 2]=MAX([Person1])))
return _a+_b 

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[YavuzDuran]

It helped me Thank you 

[YavuzDuran]

Thank you,

Is there any way to show Luis only once in the table (same for Roman)