Hi All,
I am working on an KPI which shows deviation% from planned cumulative spend vs actual cumulative spend by month for a project.
Custom threshold is as follows, if deviation% is
1. between >=-10% and <=10%, then Green
2. between <=-11% and >=11%, then Yellow
3. between <=-20% and >=20%, then Red
I inserted pivot on raw data for cumulative planned vs actual by month and did deviation% by cell referencing and custom formatting of threshold outside pivot.
However, when I select different project, I have 3 issues:
1. sometimes, the deviation% and custom R/Y/G indicator doesn't update as per above given threshold.
2. sometimes, deviation% updates but the R/Y/G indiactor doesn't update.
3. another requirement is to change the color of the deviation% in R/Y/G color. I cannot do this like making value green for both +-10%.
Someone, please guide me to a better workable formula and soultion to my 3 stated problem.
Much thanks in advance.
Regards.
I am working on an KPI which shows deviation% from planned cumulative spend vs actual cumulative spend by month for a project.
Custom threshold is as follows, if deviation% is
1. between >=-10% and <=10%, then Green
2. between <=-11% and >=11%, then Yellow
3. between <=-20% and >=20%, then Red
I inserted pivot on raw data for cumulative planned vs actual by month and did deviation% by cell referencing and custom formatting of threshold outside pivot.
However, when I select different project, I have 3 issues:
1. sometimes, the deviation% and custom R/Y/G indicator doesn't update as per above given threshold.
2. sometimes, deviation% updates but the R/Y/G indiactor doesn't update.
3. another requirement is to change the color of the deviation% in R/Y/G color. I cannot do this like making value green for both +-10%.
Someone, please guide me to a better workable formula and soultion to my 3 stated problem.
Much thanks in advance.
Regards.
