Calcite | Level 5

## Calculated Measure - Maximum of an Average measure

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).

SAS Employee

## Re: Calculated Measure - Maximum of an Average measure

Hi @Pandomania ,

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

I hope this helps!

Thank you,

Teri

Teri Patsilaras is a Senior Manager in the Global Enablement and Learning (GEL) Team within SAS R&D's Technology Transfer and Governance Division
Discussion stats