add calculate column on power bi matrix with text value

[vineetthakur]

Hi Professionals,

I have the following data source file with usage rag rating of each product (or the products bought by each customer): 

Customer	Product	RAG Rating
id1	A	Red
id2	B	Amber
id1	B	Green
id4	C	Green
id3	C	Red
id1	C	Amber
id3	B	Red

i have been able to create a power bi matrix visual below : 

Customer	A	B	C
id1	Red	Green
id2		Amber
id3		Red	Red
id4			Green

Now, i need to create a calculated column (Overall RAG) in the power bi matrix visual like below. The logic for this is that if there is a red (for at least one product )then overall rag is red, amber (if no red and at least one amber) and green (if all are green), Please can you help with any suggestions? Thank you!

Customer	A	B	C	Overall RAG
id1	Red	Green		Red
id2		Amber		Amber
id3		Red	Red	Red
id4			Green	Green

[bhanu_gautam · Accepted solution]

@vineetthakur , Create a new calculated column by going to Modelling tab

DAX
Overall RAG =
VAR CustomerID = 'YourTableName'[Customer]
VAR RedCount = CALCULATE(COUNTROWS('YourTableName'), 'YourTableName'[Customer] = CustomerID, 'YourTableName'[RAG Rating] = "Red")
VAR AmberCount = CALCULATE(COUNTROWS('YourTableName'), 'YourTableName'[Customer] = CustomerID, 'YourTableName'[RAG Rating] = "Amber")
VAR GreenCount = CALCULATE(COUNTROWS('YourTableName'), 'YourTableName'[Customer] = CustomerID, 'YourTableName'[RAG Rating] = "Green")
RETURN
IF(RedCount > 0, "Red",
IF(AmberCount > 0, "Amber",
IF(GreenCount > 0, "Green", BLANK())
)
)