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]

May be like this:

If(PriceExpression = Max(TOTAL <Category> Aggr(PriceExpression, Category, SubCategory)), Yellow())

Here replace PriceExpression with the expression you are using for your Price column.

[#3]

It is not working. Please find the attached downloaded sample. This is what I got once I implemented the expression. From the attached file, you would find that Product categories like 'Bikes' and 'Clothing' are showing wrong result [Marked the cells with yellow background]. While Product Category 'Component' does not have anything. !

[#4]

And though I am using the expression posted by, could you please help me understand if I can use the below expression as well. It give the same result:

=If(StandardCost = max(total<EnglishProductCategoryName> StandardCost), Yellow())

[Kushal_Chawda]

Slight change in Sunny's expression should work.

If(PriceExpression = Max(TOTAL <Category,SubCategory> Aggr(PriceExpression, Category, SubCategory,Product)), Yellow())

[sunny_talwar]

Would you be able to share a qvw sample to see the issue?

[#7]

Sure. Please find the attached qvw.

As I am using Desktop Edition, so I would request you post your answer in reply (and not as an attachment). Thanks for your help in advance!

[#8]

It then produces a lot of other unwanted rows. PFA qvw file !

[sunny_talwar]

Is this what you want?

Expression:

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

[#10]

No. PFA the xls. The required output has Yellow background cells.