How to apply many conditions to a measure to calculate bonus

[sakuragihana]

Hello everyone, I have a report to calculate bonus of staff. But the condition to calculate bonus is complicated.

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

Another 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

Note : 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 everyone help me to apply all of conditions for dax to calculate the bonus of staff ?

[ERD · Accepted solution]

@sakuragihana ,

bonus = 
VAR datekey = CALCULATE ( MIN ( 'Date'[DateKey] ), 'Date'[Date] = SELECTEDVALUE ( 'Table 2'[Date key] ) )
VAR new_students = SELECTEDVALUE ( 'Table 2'[# of New Student PD] )
VAR new_revenue = SELECTEDVALUE ( 'Table 2'[New Revenue PD] )
VAR re_enroll = SELECTEDVALUE ( 'Table 2'[# Student Re Enroll] )
VAR re_enroll_pct = SELECTEDVALUE ( 'Table 2'[% Re Enroll Student] ) * 100
VAR area = SELECTEDVALUE ( 'Table 2'[Area Code] )
VAR filtered_areas =
    CALCULATETABLE(
        DISTINCT('Table 1'[Area Code]),
        OR (
            'Table 1'[Area Code] IN { "HCMC", "SouthArea" } && 'Table 1'[% Act vs Target] >= 0.8,
            'Table 1'[Area Code] IN { "NewArea", "VT Area" } && 'Table 1'[% Act vs Target] >= 0.75
        )
    )
VAR bonus_1_t =
    FILTER (
        'Bonus data',
        'Bonus data'[Min] <= new_students
            && 'Bonus data'[Area Code] = area
            && 'Bonus data'[Type] = "NEW ENROLLMENT"
            && 'Bonus data'[Date key] = datekey
    )
VAR bonus_2_t =
    FILTER (
        'Bonus data',
        'Bonus data'[Min] <= new_revenue
            && 'Bonus data'[Area Code] = area
            && 'Bonus data'[Type] = "NEW REVENUE"
            && 'Bonus data'[Date key] = datekey
    )
VAR bonus_3_t = 
    FILTER ( 
        'Bonus data', 
        'Bonus data'[Min] <= re_enroll_pct
            && 'Bonus data'[Max] >= re_enroll_pct
            && 'Bonus data'[Area Code] = area
            && 'Bonus data'[Type] = "RE ENROLLMENT" 
            && 'Bonus data'[Date key] = datekey
    )
VAR bonuses_calc_1 =
    SUMX (
        ADDCOLUMNS (
            bonus_1_t,
            "@bonus_calc",
                VAR current_bonus = [Bonus ]
                VAR prev_limit_running = SUMX ( FILTER ( bonus_1_t, [Bonus ] < current_bonus ), [Max] )
                VAR students_diff = new_students - prev_limit_running
                VAR students = IF ( students_diff < 0, BLANK (), students_diff )
                RETURN
                    MIN ( [Max], students ) * [Bonus ]
        ),
        [@bonus_calc]
    ) 
VAR bonuses_calc_2 =
    SUMX (
        ADDCOLUMNS (
            bonus_2_t,
            "@bonus_calc",
                VAR current_bonus = [Bonus ]
                VAR prev_limit_running = SUMX ( FILTER ( bonus_2_t, [Bonus ] < current_bonus ), [Max] )
                VAR students_diff = new_revenue - prev_limit_running
                VAR students = IF ( students_diff < 0, BLANK (), students_diff )
                RETURN
                    MIN ( [Max], students ) * [Bonus ]
        ),
        [@bonus_calc]
    ) + 0
VAR bonus_1 = IF ( area in filtered_areas, bonuses_calc_1 )
VAR bonus_2 = IF ( area in filtered_areas, bonuses_calc_2 )
VAR bonus_3 = MAXX ( bonus_3_t, [Bonus ]) * re_enroll + 0
RETURN 
    bonus_1 + bonus_2 + bonus_3

Apart from that you need to pay attention to your data. Things like this won't allow you to see correct bonus_3, but the measure is working.

[ERD · Accepted solution]

As I've mentioned before, unfortunately I can't.

Date table contains Date column and DateKey to match all the tables.

[ERD · Accepted solution]

@sakuragihana ,

bonus = 
VAR datekey = CALCULATE ( MIN ( 'Date'[DateKey] ), 'Date'[Date] = SELECTEDVALUE ( 'Table 2'[Date key] ) )
VAR new_students = SELECTEDVALUE ( 'Table 2'[# of New Student PD] )
VAR new_revenue = SELECTEDVALUE ( 'Table 2'[New Revenue PD] )
VAR re_enroll = SELECTEDVALUE ( 'Table 2'[# Student Re Enroll] )
VAR re_enroll_pct = SELECTEDVALUE ( 'Table 2'[% Re Enroll Student] ) * 100
VAR area = SELECTEDVALUE ( 'Table 2'[Area Code] )
VAR filtered_areas =
    CALCULATETABLE(
        DISTINCT('Table 1'[Area Code]),
        OR (
            'Table 1'[Area Code] IN { "HCMC", "SouthArea" } && 'Table 1'[% Act vs Target] >= 0.8,
            'Table 1'[Area Code] IN { "NewArea", "VT Area" } && 'Table 1'[% Act vs Target] >= 0.75
        )
    )
VAR bonus_1_t =
    FILTER (
        'Bonus data',
        'Bonus data'[Min] <= new_students
            && 'Bonus data'[Area Code] = area
            && 'Bonus data'[Type] = "NEW ENROLLMENT"
            && 'Bonus data'[Date key] = datekey
    )
VAR bonus_2_t =
    FILTER (
        'Bonus data',
        'Bonus data'[Min] <= new_revenue
            && 'Bonus data'[Area Code] = area
            && 'Bonus data'[Type] = "NEW REVENUE"
            && 'Bonus data'[Date key] = datekey
    )
VAR bonus_3_t = 
    FILTER ( 
        'Bonus data', 
        'Bonus data'[Min] <= re_enroll_pct
            && 'Bonus data'[Max] >= re_enroll_pct
            && 'Bonus data'[Area Code] = area
            && 'Bonus data'[Type] = "RE ENROLLMENT" 
            && 'Bonus data'[Date key] = datekey
    )
VAR bonuses_calc_1 =
    SUMX (
        ADDCOLUMNS (
            bonus_1_t,
            "@bonus_calc",
                VAR current_bonus = [Bonus ]
                VAR prev_limit_running = SUMX ( FILTER ( bonus_1_t, [Bonus ] < current_bonus ), [Max] )
                VAR students_diff = new_students - prev_limit_running
                VAR students = IF ( students_diff < 0, BLANK (), students_diff )
                RETURN
                    MIN ( [Max], students ) * [Bonus ]
        ),
        [@bonus_calc]
    ) 
VAR bonuses_calc_2 =
    SUMX (
        ADDCOLUMNS (
            bonus_2_t,
            "@bonus_calc",
                VAR current_bonus = [Bonus ]
                VAR prev_limit_running = SUMX ( FILTER ( bonus_2_t, [Bonus ] < current_bonus ), [Max] )
                VAR students_diff = new_revenue - prev_limit_running
                VAR students = IF ( students_diff < 0, BLANK (), students_diff )
                RETURN
                    MIN ( [Max], students ) * [Bonus ]
        ),
        [@bonus_calc]
    ) + 0
VAR bonus_1 = IF ( area in filtered_areas, bonuses_calc_1 )
VAR bonus_2 = IF ( area in filtered_areas, bonuses_calc_2 )
VAR bonus_3 = MAXX ( bonus_3_t, [Bonus ]) * re_enroll + 0
RETURN 
    bonus_1 + bonus_2 + bonus_3

Apart from that you need to pay attention to your data. Things like this won't allow you to see correct bonus_3, but the measure is working.

[sakuragihana]

Hi @ERD, 

Can you sent me a power bi file ? Thank you so much

[ERD]

Unfortunately, not. Just use your file you've put here and add the measure. 

[sakuragihana]

Can you sent me a power bi file because I want to see the table Date ? 

My file is error, maybe the table Date is different of yours

[ERD · Accepted solution]

As I've mentioned before, unfortunately I can't.

Date table contains Date column and DateKey to match all the tables.

[sakuragihana]

Hi ERD, 

VAR bonus_3 = MAXX ( bonus_3_t, [Bonus ]) * re_enroll + 0

Bonus 3 may be not working , all of result of bonus 3 is zero. 

[ERD · Accepted solution]

Yes, as I've mentioned before:

Apart from that you need to pay attention to your data. Things like this won't allow you to see correct bonus_3, but the measure is working.

Correct the data and bonus 3 will be calculated correctly.

[sakuragihana]

Hi @ERD , 

In the February , the condition of [% Act vs Target] is changed

'Table 1'[% Act vs Target] >=0.8 is the same for all of area at February  . How can I change the dax without effect in January ?

[ERD]

If it is a one-time thing, you set up additional condition to check the dates. But in case your conditions for the % Act vs Target change often, I'd create a separate table to keep those conditions per month and area, otherwise your formula will be endless.