Forum Discussion

VikKos's avatar
VikKos
Regular Visitor
4 years ago
Solved

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 par...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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

     

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

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's avatar
VikKos
Regular Visitor
4 years ago

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