cancel
Showing results for
Did you mean:
Helper II

## 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 II

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 II

Hi @ tamerj1,

Super User

@sakuragihana
Ok but how should the report look like?

Helper II

Hi @ tamerj,

Report just need to calculate bonus for staff.

Super User

Have you seen the solution at the top?

Helper II

Yes, I saw that solution at the top.

Super User

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

Helper II

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 II

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 II

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 II

Thank you so much !

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors