博客:www.jiaopengzi.com
一、背景
1、之前写了一个关于入离调转的pp应用,现在个税新增专项附加扣除项目,借此写一个关于薪酬计算的案例;
2、本案例是用工作时长计算薪酬;
3、全勤奖每月200元,每个月工作时长大于等于8小时的天数大于等于20天计为全勤;
4、餐补每月300元,全勤天数不足20天按照15元每天计算,大于20天也是300元(没办法现实就这样);
5、社保缴纳比例和缴纳基数上限下限均为虚拟举例;
6、公积金缴纳比例和缴纳基数上限下限均为虚拟举例;
7、个税缴纳按照起征点为5000的最新税率计算。为了方便起见,本案例中不涉及其他税前补税前扣税后补税后扣相关工资项目。
结果图
二、数据源1、员工出勤
包含ID,姓名,出勤日期,工作时长(默认处理好的数据)。
2、时薪标准
工作时长每天不超过14小时,标准工作时长8小时,以后每小时递增如下图。
3、最新5000元起征的税率,等级7的上限为9999999是为了计算方便,实际工作中请以相应法律法规为准。
4、社保相关、公积金相关、专项附加扣除均为虚拟数据,实际工作中以相应法律法规为准。
5、根据时薪标准,PQ自定义函数计算出日薪。
自定义日薪函数
提前计算日薪 6、PQ导入日期表
7、建立关系
三、上DAX1、基本工资
基本工资:=SUM('员工出勤'[日薪])
2、餐补
餐补:=VAR D =COUNTROWS ( CALCULATETABLE ( '员工出勤', '员工出勤'[工作时长] > 4 ) )VAR S = 300 / 20RETURNIF ( D >= 20, 300, S * D )
3、全勤奖
全勤奖:=VAR D =COUNTROWS ( CALCULATETABLE ( '员工出勤', '员工出勤'[工作时长] >= 8 ) )RETURNIF ( D >= 20, 200, BLANK () )
4、专项附加扣除
专项附加扣除:=SUM('专项附加扣除'[金额])
5、公积金上限
公积金上限:=SUM('公积金相关'[基数上限])
6、公积金下限
公积金下限:=SUM('公积金相关'[基数下限])
7、公积金缴费比例
公积金缴费比例:=SUM('公积金相关'[缴费比例])
8、公积金
公积金:=VAR X = '员工出勤'[基本工资] '员工出勤'[餐补]'员工出勤'[全勤奖]VAR J =SWITCH (TRUE (),X <= '公积金相关'[公积金下限], '公积金相关'[公积金下限],X >= '公积金相关'[公积金上限], '公积金相关'[公积金下限],X)RETURNIF ( X = BLANK (), BLANK (), X * '公积金相关'[公积金缴费比例] )
9、社保上限
社保上限:=SUM('社保相关'[基数上限])
10、社保下限
社保下限:=SUM('社保相关'[基数下限])
11、社保缴费比例
社保缴费比例:=SUM('社保相关'[缴费比例])
12、社保
社保:=VAR X = '员工出勤'[基本工资] '员工出勤'[餐补]'员工出勤'[全勤奖]VAR J =SWITCH (TRUE (),X <= '社保相关'[社保下限], '社保相关'[社保下限],X >= '社保相关'[社保上限], '社保相关'[社保下限],X)RETURNIF ( X = BLANK (), BLANK (), X * '社保相关'[社保缴费比例] )
13、个税下限
个税下限:=SUM('个税'[下限])
14、个税上限
个税上限:=SUM('个税'[上限])
15、个税税率
个税税率:=SUM('个税'[税率])
16、速算扣除
速算扣除:=SUM('个税'[速算扣除数])
17、个税
个税:=VAR X = '员工出勤'[基本工资] '员工出勤'[餐补]'员工出勤'[全勤奖]- '公积金相关'[公积金]- '社保相关'[社保]- '专项附加扣除'[专项附加扣除]- 5000VAR N =SWITCH (TRUE (),CALCULATE ( '个税'[个税下限], '个税'[级数] = 1 )< X&& CALCULATE ( '个税'[个税上限], '个税'[级数] = 1 )>= X, 1,CALCULATE ( '个税'[个税下限], '个税'[级数] = 2 )< X&& CALCULATE ( '个税'[个税上限], '个税'[级数] = 2 )>= X, 2,CALCULATE ( '个税'[个税下限], '个税'[级数] = 3 )< X&& CALCULATE ( '个税'[个税上限], '个税'[级数] = 3 )>= X, 3,CALCULATE ( '个税'[个税下限], '个税'[级数] = 4 )< X&& CALCULATE ( '个税'[个税上限], '个税'[级数] = 4 )>= X, 4,CALCULATE ( '个税'[个税下限], '个税'[级数] = 5 )< X&& CALCULATE ( '个税'[个税上限], '个税'[级数] = 5 )>= X, 5,CALCULATE ( '个税'[个税下限], '个税'[级数] = 6 )< X&& CALCULATE ( '个税'[个税上限], '个税'[级数] = 6 )>= X, 6,CALCULATE ( '个税'[个税下限], '个税'[级数] = 7 )< X&& CALCULATE ( '个税'[个税上限], '个税'[级数] = 7 )>= X, 7)RETURNX* CALCULATE ( '个税'[个税税率], '个税'[级数] = N )- CALCULATE ( '个税'[速算扣除], '个税'[级数] = N )
18、实发工资
实发工资:='员工出勤'[基本工资] '员工出勤'[餐补] '员工出勤'[全勤奖]-'社保相关'[社保]-'公积金相关'[公积金]-'个税'[个税]
四、总结
1、本案例中没有复杂的DAX逻辑,都是很简单的业务转换;
2、小技巧:时薪转换为日薪的pq自定义函数;
3、参数表的使用;
4、为了方便演示,本案例不涉及其他工资项目,以及公积金超额计税等;
5、笔者并非专业HR从业人员,案例旨在分享Power Pivot相关业务落地使用案例,具体业务有不妥之处望请指正。
by 焦棚子
焦棚子的文章目录
免责声明:本网站所有信息仅供参考,不做交易和服务的根据,如自行使用本网资料发生偏差,本站概不负责,亦不负任何法律责任。涉及到版权或其他问题,请及时联系我们。