BookmarkSubscribeRSS Feed
Calcite | Level 5

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

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 Patsilaras is a Senior Manager in the Global Enablement and Learning (GEL) Team within SAS R&D's Technology Transfer and Governance Division



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1 like
  • 2 in conversation