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: INVOICE STORE SELLER AMOUNT 154 MA...
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.

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,