I have created a bubble chart that is drillable to six levels. I need to create a reference line to show the median for each level separately. The problem is when I drill down the median should represent the current chart not the first one.
The reference lines have limited functionality as you have discovered.
I want to use a parameter to control them - maybe that will make it into a future release. 🙂
Hi @abcde,
If your reference line can be the average, instead of median, I think there is a workaround.
I've recently replied to a different inquiry, in which I've explained the steps to simulate hiearchy navigation using control objects, such as drop down lists, to solve a different problem. but I think it could help with your particular case as well. The steps are explained here,
Besides implementing the series of drop down lists (5 total in your case), you would need to create an aggregated measure (let's call it Average Order Total) to be used as the reference line, that looks like this:
Observe that the expression above calculates the average (not the median) as you asked originally.
To finalize, you will need to create a custom chart that combines a bubble and a line chart, making sure they both share the same Y axis.
These are a few screenshots with the following role assignment:
Continent level:
Country level:
State level:
I hope it helps.
Best,
Renato
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.
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.