Help Needed on a complex calculation

[Anonymous]

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
12345	Johnny Appleseed	USD	2200
12345	Johnny Appleseed	USD	1500
54321	Kate Appleseed	INR	15000

The worker cannot receive more than 3x the max amount by currency. The break downs are below:

Rules

USD, EUR, CAD, or GBP = 1,000.

CNY = 3,000

INR = 10,000

JPY = 100,000

This means the max a person should earn for the bonus cannot exceed 3x more than the amounts provided above in the local currency. For example, ff we look at Johnny Appleseed, we would want a flag to say "Not eligible" next to his records to say he is no longer eligible for future bonuses given his spend is $3,700 and $3,000 was the limit for him (1000 x 3). Otherwise, if they are under spend, we want it to say "Eligible". 

I then would want to to create just a separate table visual that shows a distinct worker with their total spend and the associated cap, then the delta.  The cap is calculated based on the logic above. For example:

EE ID	Name	Currency	Total Bonus	Cap	Remaining
11111	Christina Appleseed	EUR	1000	3000	2000
12345	Johnny Appleseed	USD	3700	3000	0
54321	Kate Appleseed	INR	15000	45000	30000

Does this make sense? Any help would be great please.

Thanks!

[amitchandak]

@Anonymous , What I got from initial desc.

Two New columns like this should help

cap=
switch(true(),
[Currency] in {"USD","EUR","CAD","GBP"}, 3*1000,
[Currency]  ="CNY", 3*3000
[Currency]  ="INR" 3*10000
[Currency]  ="JPY" 3*100000)

Remaining = if([Cap]>[Total Bonus],[Cap]- [Total Bonus],0)

 if you need more help make me @

Appreciate your Kudos.

[Anonymous]

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

[Icey · Accepted solution]

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]

Thank you both -- this worked wonders. @Icey @amitchandak