Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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:

INVOICESTORESELLERAMOUNT
154MAINJohn150
155SOUTHMarie157
156NORTHPam787
157EASTJim871
158MAINDwight880
159SOUTHKim889
160NORTHJim899
161EASTDwight908
162MAINKim918
163SOUTHJim927
164NORTHDwight937
165EASTKim947
166MAINMarie957
167SOUTHPam967
168NORTHJim977
169EASTDwight987
170MAINMarie997
171SOUTHPam1008
172NORTHJim1018

 

STORE MASTER

STORESTATE
MAINDC
SOUTHTX
NORTHMN
EASTNY

 

BONUS MASTER

STATEMINAMTMAXAMTBONUS
DC0.01499.9910
DC500999.9920
DC10001000030
TX0.01349.9915
TX350749.9930
TX7501000035
MN0.01499.9912
MN500999.9924
MN10001000040
NY0.01399.998
NY400799.9916
NY80010000

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!!!!
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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,