Need help to find the dax for calculating bonus

Status
Not open for further replies.

jamemoriaty

Banned user
Joined
Feb 3, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,
I am a new member. My name is Jame. I am a Financial Accountant. I work as an Accountant. I have developed great interest in Power BI, to improve efficiency and integrity of Financial Reporting. I have been doing most of my data analytics through Excel. I hope to learn a lot from your website . I have many problems in my reports and would like to get help from experts .

I have a bonus table with many conditions to apply for calculating bonus for staff.
In table Bonus:

sakuragihana_0-1675904981916.png




The bonus table is explained as follows:

1. Bonus for new student enrollment

Bonus 1 =Number of New Student PD x Bonus of group


-Apply for condition type NEW ENROLLMENT :

1. Area code : HCMC & SouthArea

2.% Act vs Target of Campus is greater than and equal 80%

3.# of New Student PD is greater than and equal 5

sakuragihana_1-1675904981926.png


Example:

sakuragihana_2-1675904982342.png




Conditions apply for type NEW ENROLLMENT

1. Area code : New Area & VT Area

2.% Act vs Target of Campus is greater than and equal 75%

3.# of New Student PD is greater than and equal 3

sakuragihana_3-1675904982309.png




sakuragihana_4-1675904982365.png




2. Bonus for new revenue

Bonus 2 = New Revenue PD x Bonus of group


Conditions apply for type NEW REVENUE

1. Area code : HCMC & SouthArea

2.% Act vs Target of Campus is greater than and equal 80%

3.New Revenue PD is greater than and equal 50,000,000

sakuragihana_5-1675904982354.png


sakuragihana_6-1675904981927.png


Similar as above, Conditions apply for type NEW REVENUE

1. Area code : New Area & VT Area

2.% Act vs Target of Campus is greater than and equal 75%

3.New Revenue PD is greater than and equal 30,000,000

sakuragihana_7-1675904982332.png




sakuragihana_8-1675904981928.png




3. Bonus for re enrollment

Bonus 3 = # Student Re Enroll x % The corresponding bonus level according to the table


Conditions apply for type NEW REVENUE

1. Area code : all of area

2.% Re Enroll Student following percentage as :



sakuragihana_9-1675904982369.png




Example : Staff E have 30 student re enrollment and % Re Enroll Student is 120%

Bonus 3 = 30 x 20,000 = 600,000

Total Bonus = Bonus 1+ Bonus 2+Bonus 3

All of type condition are calculating for January because in February bonus will be change the value. Can the measure apply the conditions for month ?

I make a power BI file with table data and bonus table in this link : Bonus Staff.pbix

Can you help me to apply all of conditions for dax to calculate the bonus of staff ?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Status
Not open for further replies.

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top