Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I have an inline table below need to calculate increased salary For 1 Year, 2 Year and 3 year based on Salary hike given.
EMPID, Current Salary, Hike
'1', '20000', '20%'
'2', '30000', '30%'
'3', '40000', '40%'
Can anyone help with salary increase for 1st year. 2nd year, 3rd Year for employee 1 in an KPI ?
This is an urgent requirement.
Hi
Try like below
Dim: Emp ID, Current Salary, Hike
Exp:
1st Year =[Current Salary]*Hike
2nd Year =([Current Salary]+Column(1)) *Hike
3rd Year=([Current Salary]+Column(2)) *Hike
@aarav021 you can try below. Create 3 master measures for 3 Year expression and one Variable to store Hike. I have put EMPID=1 in set expression as mentioned, to make it dynamic for other employees you can remove that set (EMPID=1) so that you can select any EMPID and see the results in KPI.
For Hike, I am assuming it is in Decimal format for eg. 0.2 or 20%. If it is in Number format like 20, then divide it by 100 to make it decimal format in variable
Varible: vHike
Expression : sum({<EMPID={1}>}[Hike])
1st Year
Master Measure Name : SalaryIncrease_1Year
Expression:
=rangesum(Sum({<EMPID={1}>}CurrentSalary),Sum({<EMPID={1}>}CurrentSalary) * $(vHike))
2nd Year
Master Measure Name : SalaryIncrease_2Year
Expression:
=rangesum([SalaryIncrease_1Year],SalaryIncrease_1Year] * $(vHike))
3rd Year
Master Measure Name : SalaryIncrease_3Year
Expression:
=rangesum([SalaryIncrease_2Year],SalaryIncrease_2Year] * $(vHike))
Now, Your Master measure stores actual salary and not the actual increased salary. To get that you can now use below expression in your charts or KPI
1st Year:
=[SalaryIncrease_1Year]-Sum({<EMPID={1}>}CurrentSalary)
2nd Year:
=[SalaryIncrease_2Year]-[SalaryIncrease_1Year]
3rd Year:
=[SalaryIncrease_3Year]-[SalaryIncrease_2Year]
@aarav021 can you provide some sample data with expected output?
KPI for 1st year for first emp should show - 4000 (20% of 20000 is 4000, so salary increase for 1st year is 4,000, similary need to show for other years also, so just need an expression)
Hi
Try like below
Dim: Emp ID, Current Salary, Hike
Exp:
1st Year =[Current Salary]*Hike
2nd Year =([Current Salary]+Column(1)) *Hike
3rd Year=([Current Salary]+Column(2)) *Hike
@aarav021 you can try below. Create 3 master measures for 3 Year expression and one Variable to store Hike. I have put EMPID=1 in set expression as mentioned, to make it dynamic for other employees you can remove that set (EMPID=1) so that you can select any EMPID and see the results in KPI.
For Hike, I am assuming it is in Decimal format for eg. 0.2 or 20%. If it is in Number format like 20, then divide it by 100 to make it decimal format in variable
Varible: vHike
Expression : sum({<EMPID={1}>}[Hike])
1st Year
Master Measure Name : SalaryIncrease_1Year
Expression:
=rangesum(Sum({<EMPID={1}>}CurrentSalary),Sum({<EMPID={1}>}CurrentSalary) * $(vHike))
2nd Year
Master Measure Name : SalaryIncrease_2Year
Expression:
=rangesum([SalaryIncrease_1Year],SalaryIncrease_1Year] * $(vHike))
3rd Year
Master Measure Name : SalaryIncrease_3Year
Expression:
=rangesum([SalaryIncrease_2Year],SalaryIncrease_2Year] * $(vHike))
Now, Your Master measure stores actual salary and not the actual increased salary. To get that you can now use below expression in your charts or KPI
1st Year:
=[SalaryIncrease_1Year]-Sum({<EMPID={1}>}CurrentSalary)
2nd Year:
=[SalaryIncrease_2Year]-[SalaryIncrease_1Year]
3rd Year:
=[SalaryIncrease_3Year]-[SalaryIncrease_2Year]