Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help Needed on a complex calculation

Hi, Long time lurker here. The help has been great.    Question,   I have a series of bonus records as below: EE ID Name Currency Amount 11111 Christina Appleseed EUR 1000 1234...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Try to create [Cap] & [Remaining] as measures,not columns:

    Cap = 
SWITCH (
    TRUE (),
    MAX ( 'Table'[Currency] ) IN { "USD", "EUR", "CAD", "GBP" }, 3 * 1000,
    MAX ( 'Table'[Currency] ) = "CNY", 3 * 3000,
    MAX ( 'Table'[Currency] ) = "INR", 3 * 10000,
    MAX ( 'Table'[Currency] ) = "JPY", 3 * 100000
)

    Remaining = IF ( [Cap] > [Total Bonus], [Cap] - [Total Bonus], 0 )
    Total Bonus = SUM ( 'Table'[Amount] )

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Icey's avatar
Icey
Community Support
6 years ago

Hi Anonymous ,

 

Try to create [Cap] & [Remaining] as measures,not columns:

Cap = 
SWITCH (
    TRUE (),
    MAX ( 'Table'[Currency] ) IN { "USD", "EUR", "CAD", "GBP" }, 3 * 1000,
    MAX ( 'Table'[Currency] ) = "CNY", 3 * 3000,
    MAX ( 'Table'[Currency] ) = "INR", 3 * 10000,
    MAX ( 'Table'[Currency] ) = "JPY", 3 * 100000
)

Remaining = IF ( [Cap] > [Total Bonus], [Cap] - [Total Bonus], 0 )
Total Bonus = SUM ( 'Table'[Amount] )

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you both -- this worked wonders. Icey amitchandak