Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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 , 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.
Hi @amitchandak,
Thanks so much for this. Super helpful! I totally overthought what it should be. I revised the cap logic a little:
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |