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 IDNameCurrencyAmount
11111Christina AppleseedEUR1000
12345Johnny AppleseedUSD2200
12345Johnny AppleseedUSD1500
54321Kate AppleseedINR15000

 

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 IDNameCurrencyTotal BonusCapRemaining
11111Christina AppleseedEUR100030002000
12345Johnny AppleseedUSD370030000
54321Kate AppleseedINR150004500030000

 

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

 

Thanks!

 

 

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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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 IDNameCap
      12345Johnny Appleseed6000
       
       
       
      • 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.