How to make calculation for each column individually by using Measures

[VikKos]

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:

SalesRep	Advance payment
Bob	25000
Andy	10000
Peter	15000
John	4000
James	9000
Mary	0
Tim	0
Alex	0

SalesRep	Customer Name	Order number	_Total commission
Bob	Customer1	3802064325	14 162
Andy	Customer2	3802065113	4 145
Peter	Customer3	3802065055	21 530
John	Customer4	3802065054	6 703
James	Customer5	3802064897	24 156
James	Customer6	3802063088	7 285
James	Customer7	3802063329	1 530
Mary	Customer8	3802063715	15 779
Mary	Customer9	3802064523	9 857
Tim	Customer10	3802063776	12 568
Tim	Customer11	3802063717	2 515
Alex	Customer12	3802063350	2 986
Alex	Customer13	3802063918	3 511

Relationship via SalesRep 1 to Many

[smpa01 · Accepted solution]

@VikKos  okay let's try this 

Measure =
SUMX (
    'Advance Payments',
    VAR AdvPayment = 'Advance Payments'[Advance payment]
    VAR PlannedPayment =
        SUMX ( RELATEDTABLE ( 'Commission' ), Commission[_Total commission] ) * 0.2
    VAR Half_of_PlannedPayment =
        SUMX ( RELATEDTABLE ( 'Commission' ), Commission[_Total commission] ) * 0.1
    RETURN
        IF (
            AdvPayment > Half_of_PlannedPayment,
            Half_of_PlannedPayment,
            PlannedPayment - AdvPayment
        )
)

pbix is attached

[VikKos]

@smpa01  Now it works correctly. Thanks a lot for your help!

[smpa01]

@VikKos  the measure is producing this. What are you expecting and why?

[VikKos]

Hi @smpa01 

Total amount is supposed to be 17 394, instead of 12 673. As long as advanced payment for 3 SalesReps should not be deducted. Current table shows correct values for the rows, but incorrect total. Only in case calculated column is used I can get this correct Total amount.

Thank you!