Sales Target

[Estefania_89]

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

[amitchandak]

@Estefania_89 , refer if attached file after signature can help

[az38]

Hi @Estefania_89 

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 )

[Estefania_89]

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!