Blog: www.jiaopengzi.com
Article directory of jiaoshanzi
Please click to download the attachment
1, Background
1. I wrote a story about pp application of transfer in and out Now, special additional deduction items are added to individual income tax, so as to write a case about salary calculation;
2. In this case, the salary is calculated by working hours;
3. The full attendance award is 200 yuan per month. The number of days with working hours greater than or equal to 8 hours per month is greater than or equal to 20 days;
4. The meal allowance is 300 yuan per month. If the number of full attendance days is less than 20 days, it is calculated as 15 yuan per day, and if it is more than 20 days, it is 300 yuan (there is no way to be realistic, that's it);
5. The upper and lower limits of social security payment proportion and payment base are virtual examples;
6. The upper and lower limits of provident fund payment proportion and payment base are virtual examples;
7. The individual income tax shall be calculated according to the latest tax rate with a threshold of 5000. For convenience, this case does not involve other salary items that are deducted before tax and after tax.
Result diagram
2, Data source
1. Employee attendance
Including ID, name, attendance date and working hours (processed data by default).
2. Hourly rate
The working hours shall not exceed 14 hours per day, and the standard working hours shall be 8 hours, and the increment of each hour in the future is shown in the figure below.
3. For the latest tax rate of 5000 yuan, the upper limit of Grade 7 is 999999 for convenience of calculation. In practice, please refer to the corresponding laws and regulations.
4. Social security related, provident fund related and special additional deductions are virtual data, and the actual work shall be subject to the corresponding laws and regulations.
5. According to the hourly salary standard, the daily salary is calculated by PQ user-defined function.
Custom daily salary function
Calculate daily salary in advance
6. PQ import date table
7. Build relationships
3, Upper DAX
1. Basic salary
base pay:=SUM('Employee attendance'[per diem])
2. Meal supplement
Meal supplement:=
VAR D =
COUNTROWS ( CALCULATETABLE ( 'Employee attendance', 'Employee attendance'[Working hours] > 4 ) )
VAR S = 300 / 20
RETURN
IF ( D >= 20, 300, S * D )
3. Full attendance award
total management system:=
VAR D =
COUNTROWS ( CALCULATETABLE ( 'Employee attendance', 'Employee attendance'[Working hours] >= 8 ) )
RETURN
IF ( D >= 20, 200, BLANK () )
4. Special additional deduction
Special additional deduction:=SUM('Special additional deduction'[amount of money])
5. Upper limit of provident fund
Upper limit of provident fund:=SUM('Provident fund related'[Cardinality upper limit])
6. Lower limit of provident fund
Lower limit of provident fund:=SUM('Provident fund related'[Cardinality lower bound])
7. Contribution proportion of provident fund
Contribution proportion of provident fund:=SUM('Provident fund related'[Payment proportion])
8. Provident fund
accumulation fund:=
VAR X = 'Employee attendance'[base pay] + 'Employee attendance'[Meal supplement]+ 'Employee attendance'[total management system]
VAR J =
SWITCH (
TRUE (),
X <= 'Provident fund related'[Lower limit of provident fund], 'Provident fund related'[Lower limit of provident fund],
X >= 'Provident fund related'[Upper limit of provident fund], 'Provident fund related'[Lower limit of provident fund],
X
)
RETURN
IF ( X = BLANK (), BLANK (), X * 'Provident fund related'[Contribution proportion of provident fund] )
9. Social security ceiling
Social security ceiling:=SUM('Social security related'[Cardinality upper limit])
10. Lower limit of social security
Lower limit of social security:=SUM('Social security related'[Cardinality lower bound])
11. Proportion of social security payment
Proportion of social security payment:=SUM('Social security related'[Payment proportion])
12. Social security
social security:=
VAR X = 'Employee attendance'[base pay] + 'Employee attendance'[Meal supplement]+ 'Employee attendance'[total management system]
VAR J =
SWITCH (
TRUE (),
X <= 'Social security related'[Lower limit of social security], 'Social security related'[Lower limit of social security],
X >= 'Social security related'[Social security ceiling], 'Social security related'[Lower limit of social security],
X
)
RETURN
IF ( X = BLANK (), BLANK (), X * 'Social security related'[Proportion of social security payment] )
13. Lower limit of individual income tax
Lower limit of individual income tax:=SUM('personal income tax'[lower limit])
14. Upper limit of individual income tax
Upper limit of individual income tax:=SUM('personal income tax'[upper limit])
15. Individual income tax rate
Individual income tax rate:=SUM('personal income tax'[tax rate])
16. Quick deduction
Quick deduction:=SUM('personal income tax'[Quick calculation deduction ])
17. Individual income tax
personal income tax:=
VAR X = 'Employee attendance'[base pay]
+ 'Employee attendance'[Meal supplement]
+ 'Employee attendance'[total management system]
- 'Provident fund related'[accumulation fund]
- 'Social security related'[social security]
- 'Special additional deduction'[Special additional deduction]
- 5000
VAR N =
SWITCH (
TRUE (),
CALCULATE ( 'personal income tax'[Lower limit of individual income tax], 'personal income tax'[series] = 1 )< X&& CALCULATE ( 'personal income tax'[Upper limit of individual income tax], 'personal income tax'[series] = 1 )>= X, 1,
CALCULATE ( 'personal income tax'[Lower limit of individual income tax], 'personal income tax'[series] = 2 )< X&& CALCULATE ( 'personal income tax'[Upper limit of individual income tax], 'personal income tax'[series] = 2 )>= X, 2,
CALCULATE ( 'personal income tax'[Lower limit of individual income tax], 'personal income tax'[series] = 3 )< X&& CALCULATE ( 'personal income tax'[Upper limit of individual income tax], 'personal income tax'[series] = 3 )>= X, 3,
CALCULATE ( 'personal income tax'[Lower limit of individual income tax], 'personal income tax'[series] = 4 )< X&& CALCULATE ( 'personal income tax'[Upper limit of individual income tax], 'personal income tax'[series] = 4 )>= X, 4,
CALCULATE ( 'personal income tax'[Lower limit of individual income tax], 'personal income tax'[series] = 5 )< X&& CALCULATE ( 'personal income tax'[Upper limit of individual income tax], 'personal income tax'[series] = 5 )>= X, 5,
CALCULATE ( 'personal income tax'[Lower limit of individual income tax], 'personal income tax'[series] = 6 )< X&& CALCULATE ( 'personal income tax'[Upper limit of individual income tax], 'personal income tax'[series] = 6 )>= X, 6,
CALCULATE ( 'personal income tax'[Lower limit of individual income tax], 'personal income tax'[series] = 7 )< X&& CALCULATE ( 'personal income tax'[Upper limit of individual income tax], 'personal income tax'[series] = 7 )>= X, 7)
RETURN
X* CALCULATE ( 'personal income tax'[Individual income tax rate], 'personal income tax'[series] = N )- CALCULATE ( 'personal income tax'[Quick deduction], 'personal income tax'[series] = N )
18. Paid salary
net salary:='Employee attendance'[base pay] +'Employee attendance'[Meal supplement] +'Employee attendance'[total management system] -'Social security related'[social security] -'Provident fund related'[accumulation fund] -'personal income tax'[personal income tax]
4, Summary
1. In this case, there is no complex DAX logic, but a very simple business transformation;
2. Tips: pq user defined function for converting hourly salary to daily salary;
3. Use of parameter table;
4. For the convenience of demonstration, this case does not involve other salary items and excessive tax calculation of provident fund;
5. The author is not a professional HR practitioner. The purpose of the case is to share the application cases of Power Pivot related businesses. Please correct the specific business.
by coke shed