Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors