Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I am working on a costing overview but are challenged by adding UK National Insurance (NI) on top of the base salary for our employees. Employees are categories into different National Insurance Category Letters following the below rate structures on a weekly basis.
My data is structured in daily shifts per employee with base salary [ActualCost] and NI Category in seperate coloums as seen below.
Initial idea is to do a SWIFT/IF statement related to a seperate NI Cateogry table, however I cant get my head around it as the daily costs needs to be summed into Weekly costs in order to calculate the correct NI payable.
Thank you so much in advance.
Solved! Go to Solution.
Hi @Molin
I had the chance today to look into your file and write some code. I will send you the file in a private message. Following is the description of the solution. However, I still have doubts on whether to consider the calculations on weekly basis or accomulated monthly bases. At the end I decided to go with the weekly based calculation.
The first step was to import the tax lookup table
Then created the relationship.
Created the measures
Actual Cost = SUM ( LaborCost[ActualCost] )
NI Measure =
SUMX (
CROSSJOIN ( VALUES ( DateTable[YYYYWW] ), VALUES ( LaborCost[PayrollId] ) ),
VAR Cost = [Actual Cost]
VAR R1 = CALCULATE ( VALUES ( Tax[R1] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
VAR R2 = CALCULATE ( VALUES ( Tax[R2] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
VAR R3 = CALCULATE ( VALUES ( Tax[R3] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
RETURN
SWITCH(
TRUE(),
Cost < 120, 0,
Cost >= 120 && Cost <= 184, ( Cost - 120 ) * R1,
Cost > 184 && Cost <= 967, 64 * R1 + ( Cost - 184 ) * R2,
64 * R1 + 783 * R2 + ( Cost - 967 ) * R3
)
)
This is how the report looks like
Hi @Molin
I had the chance today to look into your file and write some code. I will send you the file in a private message. Following is the description of the solution. However, I still have doubts on whether to consider the calculations on weekly basis or accomulated monthly bases. At the end I decided to go with the weekly based calculation.
The first step was to import the tax lookup table
Then created the relationship.
Created the measures
Actual Cost = SUM ( LaborCost[ActualCost] )
NI Measure =
SUMX (
CROSSJOIN ( VALUES ( DateTable[YYYYWW] ), VALUES ( LaborCost[PayrollId] ) ),
VAR Cost = [Actual Cost]
VAR R1 = CALCULATE ( VALUES ( Tax[R1] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
VAR R2 = CALCULATE ( VALUES ( Tax[R2] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
VAR R3 = CALCULATE ( VALUES ( Tax[R3] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
RETURN
SWITCH(
TRUE(),
Cost < 120, 0,
Cost >= 120 && Cost <= 184, ( Cost - 120 ) * R1,
Cost > 184 && Cost <= 967, 64 * R1 + ( Cost - 184 ) * R2,
64 * R1 + 783 * R2 + ( Cost - 967 ) * R3
)
)
This is how the report looks like
Hi @Molin
you can iterate ove the values of week number (preferably, year week number) in the date table. Example
SUMX (
VALUES (DateTable[YearWeek]),
CALCULATE (
SWITCH(
TRUE(),
SELECTEDVALUE(Table[NICategory])="A",
(SUM ( Table[ActualCost])-184)*0.12)
)
ofcourse your calculation would be much more complicated. If you can sample file I migt be able to help you further. However, this is the general idea that you may follow. Other option is to use SUMMARIZE
Hi temerj1,
Thanks for helping out, that means alot! Iterating Year-Week number makes sense, however its getting quite complicated with the various ranges.
How do I send you a sample file?
Thanks in advance.
Hi, @Molin
I've checked your pbix file and there seems to be no better way.
I'm afraid you have to calculate them according to different situations.
Best Regards,
Community Support Team _ Eason
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
65 | |
50 | |
45 | |
20 | |
17 |