Color Coding within Hierarchy Dimension Based on Value

[Anonymous]

Hi,

I have a category dimension with 2 levels. I'm working on a stacked bar chart with both levels as dimension and Sum(Amount) as value. I want to color code the bar in a way that the largest level 2 category with in the level 1 category they belong to get the darkest color, and the 2nd, 3rd largest etc has the same color.  This way each level 1 category will have a largest level 2 category with the same color within the bar. (ie, 1245,463,356 should have the same color). I don't want only the largest level 2 category among all level 2 to have the darkest color.

Is there a way to do using expression in the chart instead of scripting?

Thanks

Yvonne

Sample Data:

Category Level 1	Category Level 2	Amount
Fruit	Apple	356
Fruit	Apple	35
Fruit	Banana	245
Fruit	Banana	54
Fruit	Strawberry	463
Vegetable	Cucumber	543
Vegetable	Broccoli	35
Vegetable	Broccoli	234
Vegetable	Potato	423
Vegetable	Carrots	1245
Drink	Beer	356
Drink	Soda	67

[sunny_talwar · Accepted solution]

May be using this expression:

=If(Rank(Sum(Amount)) = 1, RGB(0, 187, 237), ARGB(150, 0, 187, 237))