Change color of 2nd dimension based on max value on 1st dimension

[#1]

Hi -

Sample Data:

Category	SubCategory	Price
C1	S1	100
C1	S2	200
C1	S3	50
C2	S11	400
C2	S12	500
C2	S13	600
C3	S21	800
C3	S22	700

I need to represent the above data in a straight table and then change the background color of particular 'SubCategory' data-item to Yellow based on a clause.

The clause is the 'Subcategory' should have the highest price in its respective 'Category' section.

For example change the background color of S2, S13 and S21 to yellow since they have the highest price in their respective Category.

[sunny_talwar · Accepted solution]

This will work:

=If(Aggr(sum(StandardCost), EnglishProductCategoryName, EnglishProductSubCategoryName, EnglishProductName) = max(total<EnglishProductCategoryName> Aggr(Sum(StandardCost), EnglishProductCategoryName, EnglishProductSubCategoryName, EnglishProductName)), Yellow())

[#3]

And it did work. Splendid!

Just a quick question on total<EnglishProductCategoryName>...why shall we use a dimension after Total in the above scenario?