Obtain an amount from a third table with calculations between two ranges.

[dudasucx]

I have the following problem, I've been at it for the past 8 hoyrs and I can't see clearly anymore.

I have three tables as follows:

SALES MASTER:

INVOICE	STORE	SELLER	AMOUNT
154	MAIN	John	150
155	SOUTH	Marie	157
156	NORTH	Pam	787
157	EAST	Jim	871
158	MAIN	Dwight	880
159	SOUTH	Kim	889
160	NORTH	Jim	899
161	EAST	Dwight	908
162	MAIN	Kim	918
163	SOUTH	Jim	927
164	NORTH	Dwight	937
165	EAST	Kim	947
166	MAIN	Marie	957
167	SOUTH	Pam	967
168	NORTH	Jim	977
169	EAST	Dwight	987
170	MAIN	Marie	997
171	SOUTH	Pam	1008
172	NORTH	Jim	1018

STORE MASTER

STORE	STATE
MAIN	DC
SOUTH	TX
NORTH	MN
EAST	NY

BONUS MASTER

STATE	MINAMT	MAXAMT	BONUS
DC	0.01	499.99	10
DC	500	999.99	20
DC	1000	10000	30
TX	0.01	349.99	15
TX	350	749.99	30
TX	750	10000	35
MN	0.01	499.99	12
MN	500	999.99	24
MN	1000	10000	40
NY	0.01	399.99	8
NY	400	799.99	16
NY	800	10000	25

The problem is that General Management has changed the rules, and now I need to calculate the bonus for each seller according to a quota established by state (not by store, by state, don't ask me why 😉 ) I've already calculated the amount sold by each seller, but when I try to lookup the state and then the range, PowerBI does not read my state related column.

I was trying with the following formula:

BONUS2PAY =

VAR __state = 'STORE MASTER'[STATE]

VAR __SOLD = 'SALES MASTER'[AMTPERSALESMAN]

VAR __Relevant =

FILTER (

'BONUS MASTER',

'BONUS MASTER'[STATE] = __state

&& 'SALES MASTER'[MINAMT] <= __SOLD

&& 'SALES MASTER'[MAXAMT] >= __SOLD

)

RETURN

CALCULATE ( FIRSTNONBLANK ( 'SALES MASTER'[BONUS], TRUE ), __Relevant )

Where [AMTPERSALESMAN] is the formula where I am filtering per salesman.

Thank you!!!!

[Anonymous · Accepted solution]

Hi @dudasucx ,

According to this: I need to calculate the bonus for each seller according to a quota established by state

Please simply try to create a measure:

Measure = CALCULATE(MAX('BONUS MASTER'[BONUS]),FILTER('BONUS MASTER',[MINAMT]<=[AMTPERSALESMAN] && [MAXAMT]>=[AMTPERSALESMAN]&& [STATE]=MAX('STORE MASTER'[STATE])))

 Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dudasucx]

Dear Evelyn9

Following your excellent response; I have one more question, (and this is more of a personal challenge) than something required by the company at this moment. But I know it will be in the future.

There is talks that there will be now two different BONUS MASTER tables (one for Junior and one for Senior, salesmen)

I will categorize if the Salesperson is Jr or Sr in another Table:

Salesp JRorSR
John Jr
Marie Jr
Pam Sr
Jim Sr
Dwight Sr

Now the challenge is first revise if the Salesperosn is Jur Or Sr, So Ican go to the table 

BONUMASTERJr or BONUSMASTERSr, and apply your excellent formula!!!

Thanks in advance!!!!!

Cheers,