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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
      Icon for Community Support rankCommunity 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