Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sakuragihana
Helper IV
Helper IV

Help me to find the dax

Hello everyone,

I got a difficult problem. I have a table :

sakuragihana_0-1675532224671.png

 

Bonus  =Number of New Student  x Bonus of group

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

sakuragihana_1-1675532331223.png

 

 

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

1 ACCEPTED SOLUTION

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

1.png

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

View solution in original post

16 REPLIES 16
tamerj1
Super User
Super User

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

1.png

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
Super User
Super User

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

1.png

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

Hi  @tamerj1 ,

I have a table below sakuragihana_0-1675655683976.png

 

sakuragihana_1-1675655766523.png

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

 

sakuragihana_2-1675656088832.png

Bonus  =Number of New Student PD x Bonus of group

sakuragihana_0-1675657034320.png

 

 

sakuragihana_3-1675656178426.png

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

 

 

 

 

 

 

 

 

 

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

 

Hi @ tamerj1,

 

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

 

@sakuragihana 
Ok but how should the report look like?

Hi @ tamerj,

Report just need to calculate bonus for staff. 

 

@sakuragihana 

Have you seen the solution at the top?

Yes, I saw that solution at the top.

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

@tamerj1,

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

The solution at the top is not enough. 

 

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

1.png

@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 StudentBonusTotal
1050000500,000
151000001500,000
52000001000,000
30 3000,000

 

Bonus of Jame = 3.000,000

 

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

1.png

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

Hi @tamerj1 ,

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

 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 Re Enrollment

   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 : 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 ?

@tamerj1 ,

Thank you so much !

Helpful resources

Announcements
Fabric Community Conference

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.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors