topic Help me to fix my dax for calculating bonus of manager in DAX Commands and Tips

Help me to fix my dax for calculating bonus of manager

sakuragihana — Mon, 19 Jun 2023 10:46:42 GMT

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 ?

Re: Help me to fix my dax for calculating bonus of manager

tamerj1 — Mon, 19 Jun 2023 11:50:34 GMT

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?

Re: Help me to fix my dax for calculating bonus of manager

sakuragihana — Tue, 20 Jun 2023 03:15:16 GMT

Hi tamerj1 ,

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

Re: Help me to fix my dax for calculating bonus of manager

tamerj1 — Tue, 20 Jun 2023 03:31:32 GMT

sakuragihana

Sure but would you please reply to my other questions?

Re: Help me to fix my dax for calculating bonus of manager

sakuragihana — Tue, 20 Jun 2023 04:06:18 GMT

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

Re: Help me to fix my dax for calculating bonus of manager

tamerj1 — Tue, 20 Jun 2023 04:13:43 GMT

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.