Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Estefania_89
Frequent Visitor

Sales Target

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

fechaCATEGORY  SALES AMOUNT 
21/04/2020 0:00PRODUCTO 1960.000
03/04/2020 0:00PRODUCTO 21.290.755
03/04/2020 0:00PRODUCTO 31.290.755
21/04/2020 0:00PRODUCTO 11.290.755
03/04/2020 0:00PRODUCTO 21.290.755
03/04/2020 0:00PRODUCTO 31.290.755
21/04/2020 0:00PRODUCTO 11.290.755
03/04/2020 0:00PRODUCTO 21.290.755
03/04/2020 0:00PRODUCTO 31.290.755
21/04/2020 0:00PRODUCTO 11.290.755

 

Table "Bonus"

CATEGORY SALES TARGETBONUS
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
3 REPLIES 3
amitchandak
Super User
Super User

@Estefania_89 , refer if attached file after signature can help

 

az38
Community Champion
Community Champion

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 )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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   
fechaCATEGORY  SALES AMOUNT  
21/04/2020 0:00PRODUCTO 1960.000 
03/04/2020 0:00PRODUCTO 2500.000 
03/04/2020 0:00PRODUCTO 32.000.000 
21/04/2020 0:00PRODUCTO 130.000 
03/04/2020 0:00PRODUCTO 21.250.000 
03/04/2020 0:00PRODUCTO 3200.000 
21/04/2020 0:00PRODUCTO 12.390 
03/04/2020 0:00PRODUCTO 2913.000 
03/04/2020 0:00PRODUCTO 3980.200 
21/04/2020 0:00PRODUCTO 13.400 
21/04/2020 0:00PRODUCTO 42.000.000 
21/04/2020 0:00PRODUCTO 4302.000 
    
Table "Bonus"   
CATEGORY SALES TARGETBONUS 
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   
CATEGORYTOTAL SALES PER CATEGORYSALES TARGET RESULTS
PRODUCTO 1995.7901.000.000 $             -
PRODUCTO 22.663.0002.000.000 $  200.000
PRODUCTO 33.180.2003.000.000 $  300.000
PRODUCTO 42.302.0004.000.000 $             -
PRODUCTO 505000000 $             -

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.