Help me to fix my dax for calculating bonus of manager

[sakuragihana]

Hello everyone, I have 2 table below :

Table Data :

Table Bonus Scheme:

I have a dax for calculating bonus for manager :

Bonus_Manager =

VAR area  = SELECTEDVALUE('Data'[Area Code])

VAR percentage = SELECTEDVALUE('Data'[Percentage])

VAR _bonustable =

    FILTER (

        'Bonus Scheme',

        'Bonus Scheme'[AreaCode ] = area

            && percentage >= 'Bonus Scheme'[Min]

            && percentage <= 'Bonus Scheme'[Max]

    )

VAR bonus_manager =SUMMARIZE( _bonustable, 'Bonus Scheme'[Bonus Manager])

Return bonus_manager

But the dax is working not exactly

The dax can not calculate the SouthArea.

I make a power BI file with table data in this link: https://drive.google.com/file/d/1BI-7-w2X6OP7-_aQ-tE1KPCKSIpOOuzl/view?usp=drive_link 

Can everyone help me to fix this dax ?

[tamerj1]

Hi @sakuragihana 
The 68% of the south area do not exist in any of the ranges of the Bonus Scheme. Which means south area deserves no bonus. 
In the sample file, the data table contains only one month of data but in reality, I suppose it will contain several months. 

1. I guess the one-many relationship in the sample file was created automatically by power bi which was not your intention. I suppose this relationship do not exist in the real data model? Please confirm the shape of the data model.

2. Will bonus value be calculated for each month and then aggregated at area level?

3. The bonus scheme already represents an extended period of time (it applies from 2019 to 2023). Would there be different schemes for different periods that we need to deal with?

[sakuragihana]

Hi @tamerj1 ,

This is my answer :

1. This relationship in that model are existed in real data model. I have AreaCode for many campus and each campus belongs to different area

2.Yes, it will.

3.Yes, there be different schemes for this periods

Thank you

[tamerj1]

@sakuragihana 
In this case the one - many relation cannot be applied (it has to be many to many). Therefore, I can feel that you have over simplified the problem. The DAX code has to take into account all these inputs in order to work smoothly with the real data. Hence, please update the sample data reflecting all possible scenarios that need to be taken into account.

[sakuragihana]

Hi @tamerj1 ,

I want to calculate the bonus of SouthArea is 0 . How can I do that ? Can you help me ?

[tamerj1]

@sakuragihana 

Sure but would you please reply to my other questions?