RAG Logic Based on Group for a new column

[CaveOfWonders]

Dear Community

Not sure how to do the following. I have a table with a location column, a main category column, a subcategory column and a rag status:

Location	Main Category	Sub Category	RAG	Expected Ouput
Home	a	a	Red	Red
Home	a	a	Green	Red
Home	a	b	Amber	Red
Home	a	b	Amber	Red
Home	a	c	Green	Red
Home	a	c	Green	Red
Away	b	a	Amber	Amber
Away	b	a	Amber	Amber
Away	b	b	Amber	Amber
Away	b	b	Amber	Amber
Away	b	c	Green	Amber
Away	b	c	Green	Amber
Work	c	a	Green	Green
Work	c	a	Green	Green
Work	c	b	Amber	Green
Work	c	b	Amber	Green
Work	c	c	Green	Green
Work	c	c	Green	Green

I need to apply the following logic to get an overall rag status for the location and main category:

- Red Score: When a red KPI exists in a group of KPIs
- Amber Score: If there are more amber than green (No Red)
- Green Score: If there are more green than amber (No Red)

My expected result is:

Thank you

[v-yangliu-msft · Accepted solution]

Hi  @CaveOfWonders  ,
Here are the steps you can follow：

1. Create measure.

Expected Output =
var _countred=CALCULATE(COUNT('Table'[RAG]),FILTER(ALL('Table'),'Table'[Location]=MAX('Table'[Location])&&'Table'[RAG]="Red"))
var _countamber=CALCULATE(COUNT('Table'[RAG]),FILTER(ALL('Table'),'Table'[Location]=MAX('Table'[Location])&&'Table'[RAG]="Amber"))
var _countgreen=CALCULATE(COUNT('Table'[RAG]),FILTER(ALL('Table'),'Table'[Location]=MAX('Table'[Location])&&'Table'[RAG]="Green"))
return
SWITCH(
    TRUE(),
    _countred<>0,"red",
    _countred=0&&_countgreen>_countamber,"Green",
    _countred=0&&_countgreen<_countamber,"Amber")

color =
SWITCH(
    TRUE(),
    [Expected Output]="red","red",
    [Expected Output]="Green","Green",
    [Expected Output]="Amber","#FFBF00")

2. Select the [Expected Output] column and click Conditional formatting – Background color

3. Enter the Background color interface, select Format by – Field value, Base on field – measure[color]

4. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CaveOfWonders]

Thank you so so much. You are a star ⭐⭐⭐⭐⭐

[CaveOfWonders]

Is it possible to create a measure for this?