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  the measure is producing this. What are you expecting and why?

 

 

VikKos's avatar
VikKos
Regular Visitor
4 years ago

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!