How to make calculation for each column individually by using Measures

Hello!

I have the list of SalesReps that have taken advance payments for their job and those that didn’t

After some time, they have a commission earned, which need to be paid.

I need to deduct parts of the advanced payment according to specific rules for those SalesReps that took advance payment. I need to calculate each person individually and show total amount to be paid in the PowerBI report

Report model is quite complex and I cannot do these calculations in calculated columns as long as it leads to the error of circular reference. However when I try to do it with the measures total amount is incorrect, as long as the formula deducts advance payment for all amount of all SalesRep (even for those that didn’t take advance payments)

Does anyone know how to make a measure that would calculate each person individually and sum up the results later.

I’ll be very grateful for your advise how to make this measure in correct way.

Current formula:

_To be paid in December INCORRECT = VAR PlannedPayment = SUMX('Commission',Commission[_Total commission])*0.2

VAR Half_of_PlannedPayment = SUMX(Commission,Commission[_Total commission])*0.1

VAR AdvPayment = SUMX('Advance Payments','Advance Payments'[Advance payment])

RETURN

IF(AdvPayment>Half_of_PlannedPayment,Half_of_PlannedPayment,PlannedPayment-AdvPayment)

Data:

Relationship via SalesRep 1 to Many