topic How to apply many conditions to a measure to calculate bonus in DAX Commands and Tips

How to apply many conditions to a measure to calculate bonus

sakuragihana — Thu, 09 Feb 2023 10:26:40 GMT

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 ?

Re: How to apply many conditions to a measure to calculate bonus

ERD — Fri, 10 Feb 2023 08:02:20 GMT

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.

Re: How to apply many conditions to a measure to calculate bonus

sakuragihana — Fri, 10 Feb 2023 08:20:42 GMT

Hi ERD,

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

Re: How to apply many conditions to a measure to calculate bonus

ERD — Fri, 10 Feb 2023 09:49:23 GMT

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

Re: How to apply many conditions to a measure to calculate bonus

sakuragihana — Fri, 10 Feb 2023 10:18:33 GMT

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

Re: How to apply many conditions to a measure to calculate bonus

ERD — Mon, 13 Feb 2023 07:21:18 GMT

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

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

Re: How to apply many conditions to a measure to calculate bonus

sakuragihana — Mon, 13 Feb 2023 09:00:16 GMT

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.

Re: How to apply many conditions to a measure to calculate bonus

ERD — Mon, 13 Feb 2023 09:57:19 GMT

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.

Re: How to apply many conditions to a measure to calculate bonus

sakuragihana — Mon, 20 Feb 2023 07:30:09 GMT

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 ?

Re: How to apply many conditions to a measure to calculate bonus

ERD — Mon, 20 Feb 2023 08:30:06 GMT

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.