# Create calculated KPI based on other RAG KPI's

• ### Question

• We'd like to create a calculated KPI in Project Online based on several RAG fields.

For example we have the following RAG Fields

• Budget Indicator (red/amber/green)
• Scope Indicator (red/amber/green)
• Time Indicator (red/amber/green)
• Benefits Indicator (red/amber/green)
• Issue Indicator (red/amber/green)

Based on this we'd like a calculated overall status field using the following principle:

• Green: maximum two RAG indicators are Amber
• Amber: maximum three RAG indicators are Amber or Red
• Red: over four RAG indicators are Amber or Red

Is there a way to do so using a formula? And if possible how do I need to set it up?

﻿

Wednesday, April 10, 2019 2:01 PM

• Hi,

The solution from Paul definitely works, but it's based on a total score in stead of counting Red and Amber. But based on this I was able to create the following.

Three dummy fields which count the number of time an indicator is either red, amber or green:

`IIf([Time Indicator] = "Amber"; 1; 0) + IIf([Budget Indicator] = "Amber"; 1; 0) + IIf([Risk Indicator] = "Amber"; 1; 0) + IIf([Issue Indicator] = "Amber"; 1; 0) + IIf([Scope Indicator] = "Amber"; 1; 0) + IIf([Benefits Indicator] = "Amber"; 1; 0) + IIf([Tolerance Indicator] = "Amber"; 1; 0)`

Based on the counters I've created another field which calculates the score:

`IIf([RAGCalcRed] + [RAGCalcAmber] + [RAGCalcGreen] = 0; "Grey"; IIf([RAGCalcRed] + [RAGCalcAmber] > 3; "Red"; IIf([RAGCalcRed] + [RAGCalcAmber] > 1; "Amber"; IIf([RAGCalcRed] = 1; "Amber"; "Green"))))`

Thanks for the help :)

• Marked as answer by Thursday, April 18, 2019 9:35 AM
Thursday, April 18, 2019 9:35 AM

### All replies

• Hello,

You could potentially give each RAG field a score such as:

If Budgets RAG Red, 3, if Amber 2, else 1 - repeat for all RAG fields then sum up the total, so if RAG Overall greater than 10 its Red, if greater than 6 Amber etc.

Might be an option - just reply on my phone so no access. There's probably a better solution though!

Paul

Thursday, April 11, 2019 6:43 AM
• Hi Paul,

thanks for the suggestion, I was thinking about the same, but it's not ideal.

Anyone a better solution?

Thanks :)

Wednesday, April 17, 2019 10:58 AM
• S.Bots --

Umm, I thought the idea from my colleague, PW Mather, was brilliant.  It really simplifies an otherwise complex formula and graphical indicator challenge.  Out of curiosity, what is not ideal about the solution?  I think you should tell us that before any of us try to come up with an alternate solution for you.

Also, if we are going to require more information from you, would you please also tell us the following:

• The field type for each of your RAG fields (Number, Text, Cost, etc.)
• The formula you are using for each of those RAG fields
• The criteria for the RAG graphical indicators shown for each of those RAG fields

Dale A. Howard [MVP]

Wednesday, April 17, 2019 10:13 PM
• Hi,

The solution from Paul definitely works, but it's based on a total score in stead of counting Red and Amber. But based on this I was able to create the following.

Three dummy fields which count the number of time an indicator is either red, amber or green:

`IIf([Time Indicator] = "Amber"; 1; 0) + IIf([Budget Indicator] = "Amber"; 1; 0) + IIf([Risk Indicator] = "Amber"; 1; 0) + IIf([Issue Indicator] = "Amber"; 1; 0) + IIf([Scope Indicator] = "Amber"; 1; 0) + IIf([Benefits Indicator] = "Amber"; 1; 0) + IIf([Tolerance Indicator] = "Amber"; 1; 0)`

Based on the counters I've created another field which calculates the score:

`IIf([RAGCalcRed] + [RAGCalcAmber] + [RAGCalcGreen] = 0; "Grey"; IIf([RAGCalcRed] + [RAGCalcAmber] > 3; "Red"; IIf([RAGCalcRed] + [RAGCalcAmber] > 1; "Amber"; IIf([RAGCalcRed] = 1; "Amber"; "Green"))))`

Thanks for the help :)

• Marked as answer by Thursday, April 18, 2019 9:35 AM
Thursday, April 18, 2019 9:35 AM