Calculated Measure - Maximum of an Average measure

[Pandomania]

I am using SAS Viya.
I want to be able to highlight the highest (Maximum) value on a Bar chart.

The measure is Average Gross Margin which is a calculated measure of (Product Sale - Product Cost of Sale) aggregated as an average.

My Category is a Hierarchical Geographic one - Continent: Country: Region: City.

I presume I need to create a new calculated measure that can identity the Maximum of the Averages calculated at each level in the hierarchy as I drill down.

I know there is a restriction on nested aggregations. I have found the AggregateTable option which appears to get around that nesting restriction. But I can't get it to work.

This is what I did....

Or as Text: AggregateTable(_Max_, Table(_Avg_, Fixed(), ( 'Product Sale'n - 'Product Cost of Sale'n )))

I thought this would give the Maximum of the Average amounts. But it gives me the overall Average for the level of the hierarchy I am currently in. I can use this to highlight all the category values that are above or below the average for that level. But I want to be able to highlight the category with the highest value (Maximum).

I can create a Rule to highlight categories using my new measure, but it identifies all that are above the overall average for the hierarchy level.
In the diagram below, the Average Gross Margin across all continents is $3.15. 

In the Bar chart the first three bars all have a value of >= $3.15.

How can I create a measure that will allow me to only highlight the longest bar (South America)? $5.95...

...and then as I drill down to Country level only highlight Peru ($6.35).

[TeriPatsilaras]

Hi @Pandomania ,

I wrote an article outlining how I used the AggregateCells operator with Display Rules to get this solution:

See the steps here:  VA Report Example: Use a Display Rule to highlight the maximum of an average measure 

I hope this helps!

Thank you,

Teri