SAS VA Reference Line

[abcde]

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. 

[TriciaAanderud]

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

[Renato_sas]

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