Forum Discussion
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...
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.
Hi amitchandak,
Thanks so much for this. Super helpful! I totally overthought what it should be. I revised the cap logic a little:
Cap =
switch(
true(),
OTP[Currency] in {"USD","EUR","CAD","GBP"}, 3*1000,
OTP[Currency] ="CNY", 3*3000,
OTP[Currency] ="INR", 3*10000,
OTP[Currency] ="JPY", 3*100000,
Blank()
)
However whether I use yours or mine, when I put a matrix table, it doubles the cap based on how many records that person has.
So if you look at the very top, Johnny Appleseed has two transactions of bonus.
So when I throw his name in a matrix visual with the CAP calc column, it says he has a 6000 cap instead of a 3000.
How do I fix that?
People may have multiple bonuses so the cap can only be one value. If he gets another bonus, the cap would then increase to 9000, which should not happen. It should always be 3000. Because I then want to sum the bonus amounts to see the delta between cap and spend.
| EE ID | Name | Cap |
| 12345 | Johnny Appleseed | 6000 |
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.
Thank you both -- this worked wonders. Icey amitchandak