Sales Target

Question

Hello every one!&nbsp;Could I have help with this problem?. I have a table called&nbsp; "SALES"&nbsp; and it&nbsp; is a&nbsp; registre of&nbsp; the sales per category of products . In another table called&nbsp; "BONUS" i have&nbsp; the sales goals by category and the bonus they earn if they achieve they sales goal.I need a measure that&nbsp; returns the bonus if the goal was achieved and 0 if it doesn´t&nbsp;&nbsp;TABLE&nbsp; SALESfechaCATEGORY&nbsp;&nbsp;SALES AMOUNT&nbsp;21/04/2020 0:00PRODUCTO 1960.00003/04/2020 0:00PRODUCTO 21.290.75503/04/2020 0:00PRODUCTO 31.290.75521/04/2020 0:00PRODUCTO 11.290.75503/04/2020 0:00PRODUCTO 21.290.75503/04/2020 0:00PRODUCTO 31.290.75521/04/2020 0:00PRODUCTO 11.290.75503/04/2020 0:00PRODUCTO 21.290.75503/04/2020 0:00PRODUCTO 31.290.75521/04/2020 0:00PRODUCTO 11.290.755&nbsp;Table "Bonus"CATEGORY&nbsp;SALES TARGETBONUSPRODUCTO 1&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.000PRODUCTO 2&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200.000PRODUCTO 3&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300.000PRODUCTO 4&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 400.000PRODUCTO 5&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 500.000

az38 · Answer

Hi&nbsp;Estefania_89&nbsp;
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 &gt;= _target, CALCULATE(MAX(Bonus[BONUS]), Bonus[Category] = _curCategory ), 0 )

amitchandak · Answer

Estefania_89 , refer if attached file after signature can help
&nbsp;

estefania_89 · Answer

Hello,&nbsp;&nbsp;i tried to apply your solution, but this messege came up when i was writting this var:&nbsp;&nbsp;var _target = CALCULATE(MAX(Bonus[Sales target]), Bonus[Category] = _curCategory )&nbsp;Error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.&nbsp;I would like to confirm with you if the solution is what i am looking for, so I have written&nbsp; an example in case is useful:&nbsp;&nbsp;TABLE&nbsp; SALES&nbsp;&nbsp;&nbsp;fechaCATEGORY&nbsp;&nbsp;SALES AMOUNT&nbsp;&nbsp;21/04/2020 0:00PRODUCTO 1960.000&nbsp;03/04/2020 0:00PRODUCTO 2500.000&nbsp;03/04/2020 0:00PRODUCTO 32.000.000&nbsp;21/04/2020 0:00PRODUCTO 130.000&nbsp;03/04/2020 0:00PRODUCTO 21.250.000&nbsp;03/04/2020 0:00PRODUCTO 3200.000&nbsp;21/04/2020 0:00PRODUCTO 12.390&nbsp;03/04/2020 0:00PRODUCTO 2913.000&nbsp;03/04/2020 0:00PRODUCTO 3980.200&nbsp;21/04/2020 0:00PRODUCTO 13.400&nbsp;21/04/2020 0:00PRODUCTO 42.000.000&nbsp;21/04/2020 0:00PRODUCTO 4302.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table "Bonus"&nbsp;&nbsp;&nbsp;CATEGORY&nbsp;SALES TARGETBONUS&nbsp;PRODUCTO 1&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.000&nbsp;PRODUCTO 2&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200.000&nbsp;PRODUCTO 3&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300.000&nbsp;PRODUCTO 4&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 400.000&nbsp;PRODUCTO 5&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 500.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SOLUTION&nbsp;&nbsp;&nbsp;CATEGORYTOTAL SALES PER CATEGORYSALES TARGET&nbsp;RESULTSPRODUCTO 1995.7901.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -PRODUCTO 22.663.0002.000.000&nbsp;$&nbsp; 200.000PRODUCTO 33.180.2003.000.000&nbsp;$&nbsp; 300.000PRODUCTO 42.302.0004.000.000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -PRODUCTO 505000000&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;So the total sale should be compared to the target sale and the measure return the bonus if the target sale was achievedThank yoy very much for your help!

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

Forum Discussion

Sales Target

3 Replies

Related content

Sales Actuals and Targets

Daily Sales Target Calculation

Sales/Manual targets, depending on a grouped value

Calculate Sales Tracking against target

Sales / Target Issue

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 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	$ -