Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello every one!
Could I have help with this problem?. I have a table called "SALES" and it is a registre of the sales per category of products . In another table called "BONUS" i have the sales goals by category and the bonus they earn if they achieve they sales goal.
I need a measure that returns the bonus if the goal was achieved and 0 if it doesn´t
TABLE SALES
fecha | CATEGORY | SALES AMOUNT |
21/04/2020 0:00 | PRODUCTO 1 | 960.000 |
03/04/2020 0:00 | PRODUCTO 2 | 1.290.755 |
03/04/2020 0:00 | PRODUCTO 3 | 1.290.755 |
21/04/2020 0:00 | PRODUCTO 1 | 1.290.755 |
03/04/2020 0:00 | PRODUCTO 2 | 1.290.755 |
03/04/2020 0:00 | PRODUCTO 3 | 1.290.755 |
21/04/2020 0:00 | PRODUCTO 1 | 1.290.755 |
03/04/2020 0:00 | PRODUCTO 2 | 1.290.755 |
03/04/2020 0:00 | PRODUCTO 3 | 1.290.755 |
21/04/2020 0:00 | PRODUCTO 1 | 1.290.755 |
Table "Bonus"
CATEGORY | SALES TARGET | BONUS |
PRODUCTO 1 | $ 1.000.000 | $ 100.000 |
PRODUCTO 2 | $ 2.000.000 | $ 200.000 |
PRODUCTO 3 | $ 3.000.000 | $ 300.000 |
PRODUCTO 4 | $ 4.000.000 | $ 400.000 |
PRODUCTO 5 | $ 5.000.000 | $ 500.000 |
@Estefania_89 , refer if attached file after signature can help
add a measure in Sales table
isBonus Measure =
var _curCategory = MAX(Sales[CATEGORY])
var _totalSales = CALCULATE(SUM(Sales[SALES AMOUNT]), ALLEXCEPT(Sales, Sales[CATEGORY]) )
var _target = CALCULATE(MAX(Bonus[Sales target]), Bonus[Category] = _curCategory )
RETURN
IF(_totalSales >= _target, CALCULATE(MAX(Bonus[BONUS]), Bonus[Category] = _curCategory ), 0 )
Hello,
i tried to apply your solution, but this messege came up when i was writting this var:
var _target = CALCULATE(MAX(Bonus[Sales target]), Bonus[Category] = _curCategory )
Error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
I would like to confirm with you if the solution is what i am looking for, so I have written an example in case is useful:
TABLE SALES | |||
fecha | CATEGORY | SALES AMOUNT | |
21/04/2020 0:00 | PRODUCTO 1 | 960.000 | |
03/04/2020 0:00 | PRODUCTO 2 | 500.000 | |
03/04/2020 0:00 | PRODUCTO 3 | 2.000.000 | |
21/04/2020 0:00 | PRODUCTO 1 | 30.000 | |
03/04/2020 0:00 | PRODUCTO 2 | 1.250.000 | |
03/04/2020 0:00 | PRODUCTO 3 | 200.000 | |
21/04/2020 0:00 | PRODUCTO 1 | 2.390 | |
03/04/2020 0:00 | PRODUCTO 2 | 913.000 | |
03/04/2020 0:00 | PRODUCTO 3 | 980.200 | |
21/04/2020 0:00 | PRODUCTO 1 | 3.400 | |
21/04/2020 0:00 | PRODUCTO 4 | 2.000.000 | |
21/04/2020 0:00 | PRODUCTO 4 | 302.000 | |
Table "Bonus" | |||
CATEGORY | SALES TARGET | BONUS | |
PRODUCTO 1 | $ 1.000.000 | $ 100.000 | |
PRODUCTO 2 | $ 2.000.000 | $ 200.000 | |
PRODUCTO 3 | $ 3.000.000 | $ 300.000 | |
PRODUCTO 4 | $ 4.000.000 | $ 400.000 | |
PRODUCTO 5 | $ 5.000.000 | $ 500.000 | |
SOLUTION | |||
CATEGORY | TOTAL SALES PER CATEGORY | SALES TARGET | RESULTS |
PRODUCTO 1 | 995.790 | 1.000.000 | $ - |
PRODUCTO 2 | 2.663.000 | 2.000.000 | $ 200.000 |
PRODUCTO 3 | 3.180.200 | 3.000.000 | $ 300.000 |
PRODUCTO 4 | 2.302.000 | 4.000.000 | $ - |
PRODUCTO 5 | 0 | 5000000 | $ - |
So the total sale should be compared to the target sale and the measure return the bonus if the target sale was achieved
Thank yoy very much for your help!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |