Help me to find the dax

[sakuragihana]

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:

Please help me to find a dax for this calculation. Thank you so much

[tamerj1]

@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 = 
            ADDCOLUMNS ( 
                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

[tamerj1]

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 = 
        ADDCOLUMNS ( 
            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

[sakuragihana]

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.

Please help me to apply 3 conditions for dax to calculate bonus for staff  Jame

[tamerj1]

@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.

[sakuragihana]

Hi @ tamerj1,

Thank you for your help, I share PBIX file:  https://drive.google.com/file/d/19AzUu3NehGDvZPWXJpiT-xaGKOPg4tf4/view?usp=share_link 

[tamerj1]

@sakuragihana 
Ok but how should the report look like?

[sakuragihana]

Hi @ tamerj,

Report just need to calculate bonus for staff. 

[tamerj1]

@sakuragihana 

Have you seen the solution at the top?

[sakuragihana]

Yes, I saw that solution at the top.

[tamerj1]

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

[sakuragihana]

@tamerj1,

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

The solution at the top is not enough. 

[tamerj1]

@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.

[sakuragihana]

@tamerj1 ,

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

[tamerj1 · Accepted solution]

@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 = 
            ADDCOLUMNS ( 
                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

[sakuragihana]

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 ?

[sakuragihana]

@tamerj1 ,

Thank you so much !