I can't make a dax for calculating bonus

[sakuragihana]

Hello everyone,

I got a difficult problem. I have 2 table with data and some conditions for calculating bonus of employee. Please help me to find the measure for calculating this bonus.

Table 1 :

Campus Code	User Name	Area Code	Enquiries	# of New Student PD	Target New Enrol PD	% Act vs Target	New Revenue PD	Month
ADV	ADV	MiddleArea	56	45	28	161%	524,865,000	Jan
BD-CMT8	BD-CMT8	SouthArea	13	6	12	50%	71,233,000	Jan
HCM_NK2	HCM_NK2	NorthArea	40	47	35	134%	528,058,000	Jan

Table 2 : 

Campus Code	User Name	Area Code	Enquiries	# of New Student PD	New Revenue PD	Month
ADV	Jame	MiddleArea	31	30	318,159,000	Jan
ADV	Mary	MiddleArea	11	8	103, 617,000	Jan
ADV	Paul	MiddleArea	14	7	103,089,000	Jan
BD-CMT8	Anna	SouthArea	9	2	19,800,000	Jan
BD-CMT8	Lionel	SouthArea	3	2	29,333,000	Jan
BD-CMT8	Paredas	SouthArea		1	700,000	Jan
BD-CMT8	Morgan	SouthArea	1	1	21,400,000	Jan
HCM_NK2	Kitamura	NorthArea	13	20	199,950,000	Jan
HCM_NK2	Ronald	NorthArea	11	13	192,158,000	Jan
HCM_NK2	Mackinsey	NorthArea	12	8	70,200,000	Jan
HCM_NK2	Alex	NorthArea	4	3	44,450,000	Jan
HCM_NK2	David	NorthArea		1	7,100,000	Jan
HCM_NK2	Arriel	NorthArea		1	7,100,000	Jan
HCM_NK2	Cole	NorthArea		1	7,100,000	Jan

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

Group	Group 1	Group 2	Group 3	Group 4	Group 5
Number of new Student PD	5-10	11-15	16 - 20	21 - 25	>26
Bonus	50,000	100,000	200,000	300,000	500,000

Bonus 1 =Number of New Student PD x Bonus of group

Example 1 : Employee A has 32 new students enrolled

Number of Student	Bonus	Total
10	50,000	500,000
15	100,000	1,500,000
7	200,000	1,400,000
32		3,400,000

Example 2 : Employee B has 90 new students enrolled

Number of Student	Bonus	Total
10	50,000	500,000
15	100,000	1,500,000
20	200,000	4,000,000
25	300,000	7,500,000
20	500,000	10,000,000
90		23,500,000

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. Bonus for Jame

Number of Student	Bonus	Total
10	50,000	500,000
15	100,000	1,500,000
5	200,000	1,000,000
30		3,000,000

Bonus 1 of Jame : 3,000,000

2.Bonus according to revenue

Condition 1	Apply for Area code MiddeArea and NorthArea
Condition 2	% Act vs Target of Campus is greater than and equal 80%
Condition 3	New Revenue PD is greater than and equal 50,000,000

Group	Group 1	Group 2	Group 3	Group 4	Group 5
New Revenue PD	50,000,000-100,000,000	100,000,000-250,000,000	250,000,000-350,000,000	350,000,000 - 500,000,000	>500,000,000
Bonus	0.50%	2.50%	5%	7.50%	10%

Bonus 2 = New Revenue PD x Bonus of group

Example : Employee C has 20 new students enrolled with tuition revenue of 425,000,000

New Revenue PD	Bonus	Total
100,000,000	0.50%	500,000
250,000,000	2.50%	6,250,000
75,000,000	5%	3,750,000
425,000,000		10,500,000

In this table, Jame belong to Campus ADV which Area Code is MiddeArea and % Act vs Target 161% and New Revenue PD is 318,159,000. All of 3 conditions are satisfied.

New Revenue PD	Bonus	Total
100,000,000	0.50%	500,000
218,159,000	2.50%	5,453,975
318,159,000		5,953,975

Bonus 2 of jame : 5,953,975

Total Bonus = Bonus 1 + Bonus 2

Bonus of Jame = 8,953,975

I post this data in this link : https://docs.google.com/spreadsheets/d/1oa_1YAC9osMY5jTjtzya-rVtsaJBe-BR/edit?usp=share_link&ouid=11... 

Please help me to find the measure for calculating this bonus . Thank you so much !

[ERD · Accepted solution]

@sakuragihana ,

You need to have 2 columns for the limits and change the table structure a little. This can be done in Power Query, I will add the steps below how to achieve this from your current tables:

Bonus for new student enroll:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcwxCsMwDAXQqwjPNkhynQOUQrZS6Oh6KXW22CGNyPUbhdSZ/uOjrxhNP1eZwNgDdJJP+qZLUzDJRnOX8Z1nqAOUvMJzkU8uCzxu21lwhFsQOQqaHThgbZhU2r0E0Wfu9lfXWuSrO7SI+xL/4ibfFA6l9AM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Number of new Student PD", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Number of new Student PD.1", "Number of new Student PD.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Number of new Student PD.1", Text.Trim, type text}, {"Number of new Student PD.2", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",">","",Replacer.ReplaceText,{"Number of new Student PD.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"9999999",Replacer.ReplaceValue,{"Number of new Student PD.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Number of new Student PD.2", Int64.Type}, {"Number of new Student PD.1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Number of new Student PD.1", "LowerLimit"}, {"Number of new Student PD.2", "UpperLimit"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Bonus", Int64.Type}})
in
    #"Changed Type1"

Bonus according to revenue:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY6xCoUwDEV/JRTcWkmtwV0ENxHX2jGritL3ft8qpa1DknNzIMRaMZ67P0DICDpjk9EkahORcNKKif+w8I83zzAPQRJKxLeUxsRBFEk1VJoiKfMxRQIF9Lm3ekTDeafff/p981ewWBNWz+046WldDDp0524=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",">","",Replacer.ReplaceText,{"New Revenue PD"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "New Revenue PD", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"New Revenue PD.1", "New Revenue PD.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"New Revenue PD.1", Text.Trim, type text}, {"New Revenue PD.2", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"New Revenue PD.1", Int64.Type}, {"New Revenue PD.2", Int64.Type}, {"Bonus", Percentage.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,9999999999,Replacer.ReplaceValue,{"New Revenue PD.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"New Revenue PD.1", "LowerLimit"}, {"New Revenue PD.2", "UpperLimit"}})
in
    #"Renamed Columns"

 Now the measure:

Bonus total = 
VAR current_campus = SELECTEDVALUE ( 'Table 2'[Campus Code] )
VAR campuses_1 =
    CALCULATETABLE (
        DISTINCT ( 'Table 1'[Campus Code] ),
        'Table 1'[Campus Code] = current_campus,
        'Table 1'[Area Code] IN { "MiddleArea", "NorthArea" },
        'Table 1'[% Act vs Target] >= 0.8,
        'Table 1'[# of New Student PD] >= 5
    )
VAR new_students = SELECTEDVALUE ( 'Table 2'[# of New Student PD] )
VAR bonus_1 = FILTER ( 'Bonus 1', 'Bonus 1'[LowerLimit] <= new_students )
VAR bonuses_calc_1 =
    ADDCOLUMNS (
        bonus_1,
        "@bonus_calc",
            VAR current_bonus = [Bonus]
            VAR prev_limit_running = SUMX ( FILTER ( bonus_1, [Bonus] < current_bonus ), [UpperLimit] )
            VAR students_diff = new_students - prev_limit_running
            VAR students = IF ( students_diff < 0, BLANK (), students_diff )
            RETURN
                MIN ( [UpperLimit], students ) * [Bonus]
    )
VAR camouses_2 =
    CALCULATETABLE (
        DISTINCT ( 'Table 1'[Campus Code] ),
        'Table 1'[Campus Code] = current_campus,
        'Table 1'[Area Code] IN { "MiddleArea", "NorthArea" },
        'Table 1'[% Act vs Target] >= 0.8,
        'Table 1'[New Revenue PD] >= 50000000
    )
VAR new_revenue = SELECTEDVALUE ( 'Table 2'[New Revenue PD] )
VAR bonus_2 = FILTER ( 'Bonus 2', 'Bonus 2'[LowerLimit] <= new_revenue )
VAR bonuses_calc_2 =
    ADDCOLUMNS (
        bonus_2,
        "@bonus_calc",
            VAR current_bonus = [Bonus]
            VAR prev_limit_running = SUMX ( FILTER ( bonus_2, [Bonus] < current_bonus ), [UpperLimit] )
            VAR revenue_diff = new_revenue - prev_limit_running
            VAR revenue = IF ( revenue_diff < 0, BLANK (), revenue_diff )
            RETURN
                MIN ( [UpperLimit], revenue ) * [Bonus]
    )
VAR Bonus_for_new_student_enroll = IF ( current_campus IN campuses_1, SUMX ( bonuses_calc_1, [@bonus_calc] ) )
VAR New_Revenue_PD_x_Bonus_of_group = IF ( current_campus IN camouses_2, SUMX ( bonuses_calc_2, [@bonus_calc] ) )
RETURN
    Bonus_for_new_student_enroll + New_Revenue_PD_x_Bonus_of_group

[sakuragihana]

Hi @ERD ,

If I apply a condition date key and type for Bonus table like that :

Bonus 1:

Group	LowerLimit	UpperLimit	Bonus	Date key	Type
1	5	10	50,000	01/01/2023	New Enrollment
2	11	15	100,000	01/01/2023	New Enrollment
3	16	20	200,000	01/01/2023	New Enrollment
4	21	25	300,000	01/01/2023	New Enrollment
5	26	99999	500,000	01/01/2023	New Enrollment

Bonus 2 : 

Group	LowerLimit	UpperLimit	Bonus	Date key	Type
1	50,000,000	100,000,000	0.50%	01/01/2023	New Revenue
2	100,000,000	250,000,000	2.50%	01/01/2023	New Revenue
3	250,000,000	350,000,000	5.00%	01/01/2023	New Revenue
4	350,000,000	500,000,000	7.50%	01/01/2023	New Revenue
5	500,000,000	999,999,999	10.00%	01/01/2023	New Revenue

I use this date key and type for condition because in every months of year, bonus will change and in the future maybe have a new type

Example: (datekey format : dd/mm/yyyy)
Bonus 1

Group	LowerLimit	UpperLimit	Bonus	Date key	Type
1	5	10	80,000	01/02/2023	New Enrollment
2	11	15	120,000	01/02/2023	New Enrollment
3	16	20	250,000	01/02/2023	New Enrollment
4	21	25	350,000	01/02/2023	New Enrollment
5	26	99999	600,000	01/02/2023	New Enrollment

Bonus 2:

Group	LowerLimit	UpperLimit	Bonus	Date key	Type
1	50,000,000	100,000,000	0.80%	01/02/2023	New Revenue
2	100,000,000	250,000,000	3.50%	01/02/2023	New Revenue
3	250,000,000	350,000,000	6.00%	01/02/2023	New Revenue
4	350,000,000	500,000,000	8.50%	01/02/2023	New Revenue
5	500,000,000	999,999,999	12.00%	01/02/2023	New Revenue

But when I apply date key condition and type conditions on measure , it make a wrong. Can you help me ?

[ERD]

@sakuragihana , that's a bit another story. You need to have a date table, columns with common date format in all tables.

[sakuragihana]

Hi @ERD ,

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]

Hi ERD,

Thank you so much

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523