cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper IV

## Help me to find the dax

Hello everyone,

I got a difficult problem. I have a table :

Bonus  =Number of New Student  x Bonus of group

Example :  Employee B has 90 new students enrolled. Bonus for B are calculated as:

1 ACCEPTED SOLUTION
Super User

@sakuragihana
Yes you are right. There was a typo mistake "MiddeArea " and the formula referenced the wrong column [# of New Student PD].

``````Bonus Amount =
VAR FilteredStaff =
FILTER (
Staff,
RELATED ( Campus[Area Code] ) IN { "MiddleArea", "NorthArea" }
&& RELATED ( Campus[% Act vs Target] ) >= 0.8
)
RETURN
SUMX (
FilteredStaff,
VAR NumOfStudents = Staff[# of New Student PD]
VAR BonusTable = FILTER ( Bonus, Bonus[Lower Limit] <= NumOfStudents )
VAR BonusTable1 =
BonusTable,
"@Students",
VAR TableBefore = FILTER ( BonusTable, [Bonus] < EARLIER ( [Bonus] ) )
VAR StudentsBefore = SUMX ( TableBefore, [Upper Limit] )
RETURN
NumOfStudents - StudentsBefore
)
VAR BonusTable2 = FILTER ( BonusTable1, [@Students] > 0 )
RETURN
SUMX (
BonusTable2,
MIN ( [Upper Limit], [@Students] ) * [Bonus]
)
) + 0``````
16 REPLIES 16
Super User

@sakuragihana
Please refer to attached sample file amended with the solution

``````Bonus Amount =
VAR FilteredStaff =
FILTER (
Staff,
RELATED ( Campus[Area Code] ) IN { "MiddeArea ", "NorthArea" }
&& RELATED ( Campus[# of New Student PD] ) >= 0.8
)
RETURN
SUMX (
FilteredStaff,
VAR NumOfStudents = Staff[# of New Student PD]
VAR BonusTable = FILTER ( Bonus, Bonus[Lower Limit] <= NumOfStudents )
VAR BonusTable1 =
BonusTable,
"@Students",
VAR TableBefore = FILTER ( BonusTable, [Bonus] < EARLIER ( [Bonus] ) )
VAR StudentsBefore = SUMX ( TableBefore, [Upper Limit] )
RETURN
NumOfStudents - StudentsBefore
)
VAR BonusTable2 = FILTER ( BonusTable1, [@Students] > 0 )
RETURN
SUMX (
BonusTable2,
MIN ( [Upper Limit], [@Students] ) * [Bonus]
)
) + 0``````
Super User

Hi @sakuragihana
Please refer to attached sample file with the solution. You need to restructure the 'Bonus' table as per below screenshots.

``````Bonus Amount =
SUMX (
'Table',
VAR NumOfStudents = 'Table'[Number of Students]
VAR BonusTable = FILTER ( Bonus, Bonus[Lower Limit] <= NumOfStudents )
VAR BonusTable1 =
BonusTable,
"@Students",
VAR TableBefore = FILTER ( BonusTable, [Bonus] < EARLIER ( [Bonus] ) )
VAR StudentsBefore = SUMX ( TableBefore, [Upper Limit] )
RETURN
NumOfStudents - StudentsBefore
)
VAR BonusTable2 = FILTER ( BonusTable1, [@Students] > 0 )
RETURN
SUMX (
BonusTable2,
MIN ( [Upper Limit], [@Students] ) * [Bonus]
)
) + 0``````
Helper IV

Hi  @tamerj1 ,

I have a table below

Bonus for new student enroll

 Condition 1 Apply for Area code MiddeArea and NorthArea Condition 2 % Act vs Target of Campus is greater than and equal 80% Condition 3 # of New Student PD is greater than and equal 5

Bonus  =Number of New Student PD x Bonus of group

Jame belong to Campus ADV which Area Code is MiddeArea and % Act vs Target 161% and # of New Student PD is 30. All of 3 conditions are satisfied.

Super User

@sakuragihana
I knew it! That was a trap 😅

Just kidding 😁

In order to help you faster please create a sample PBIX file containing a data model with similar relationships and share it with me.

Helper IV

Hi @ tamerj1,

Super User

@sakuragihana
Ok but how should the report look like?

Helper IV

Hi @ tamerj,

Report just need to calculate bonus for staff.

Super User

Have you seen the solution at the top?

Helper IV

Yes, I saw that solution at the top.

Super User

@sakuragihana
So this is not what you're looking for?

Helper IV

Yes, I need a solution for apply 3 conditions for calculating bonus of staff.

The solution at the top is not enough.

Super User

@sakuragihana
Can you please indicate in this screenshot which row are not calculated correctly along with the clarification perhaps I can better understand your requirement.

Helper IV

The condition in Filter maybe wrong :

Campus[# of New student PD ] > = 5 ( not >=0.8 )

and Campus[%Act vs Target] >=0.8

 Number of Student Bonus Total 10 50000 500,000 15 100000 1500,000 5 200000 1000,000 30 3000,000

Bonus of Jame = 3.000,000

Super User

@sakuragihana
Yes you are right. There was a typo mistake "MiddeArea " and the formula referenced the wrong column [# of New Student PD].

``````Bonus Amount =
VAR FilteredStaff =
FILTER (
Staff,
RELATED ( Campus[Area Code] ) IN { "MiddleArea", "NorthArea" }
&& RELATED ( Campus[% Act vs Target] ) >= 0.8
)
RETURN
SUMX (
FilteredStaff,
VAR NumOfStudents = Staff[# of New Student PD]
VAR BonusTable = FILTER ( Bonus, Bonus[Lower Limit] <= NumOfStudents )
VAR BonusTable1 =
BonusTable,
"@Students",
VAR TableBefore = FILTER ( BonusTable, [Bonus] < EARLIER ( [Bonus] ) )
VAR StudentsBefore = SUMX ( TableBefore, [Upper Limit] )
RETURN
NumOfStudents - StudentsBefore
)
VAR BonusTable2 = FILTER ( BonusTable1, [@Students] > 0 )
RETURN
SUMX (
BonusTable2,
MIN ( [Upper Limit], [@Students] ) * [Bonus]
)
) + 0``````
Helper IV

Hi @tamerj1 ,

I have a change request from the company, bonus for staffs have many conditions more.

In table Bonus:

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

Example:

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

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

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

3. Bonus for re enrollment

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

Conditions apply for type Re Enrollment

1. Area code : all of area

2.% Re Enroll Student following percentage as :

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 : https://drive.google.com/file/d/1N3bYrYKtcwPUffFFsJWEe_GpJee3rfT7/view?usp=share_link

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

Helper IV

Thank you so much !

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.