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