In my current project we have to calculate certain KPIs of my company and have to compare them with target value and put the result in Green or yellow or red zone using exception.
EX:- KPI 1 = (# of associates having completed training)/(Total # of planned associates to attend training)×100
So here first I have to calculate KPI 1 and put the result % in KPI and then if you see below table as next step
Target Value KPI Color zone
Pharma 10% 20% Green
America 20% 30% Green
US 50% 40% Yellow
Europe 30% 15% Red
CH 40% 20% RED
DE 50% 10% RED
In above table we need to check the KPIs
Green if KPI>Target value
Yellow if KPI is + or - 10% of target value
Red if KPI is < 10% of Target value
Now my question is how to calculate the KPI and target value difference, how to put them in Exception to get color zones display in report using above 3 conditions?