5 Replies Latest reply on Sep 2, 2016 7:12 AM by Yunus Gittham

# Drawing a AVG Reference line based on a a parameter

I will have a Parameter - Show Reference Line With Values Show/Hide.

The X axis contains Date of Service

the Y Axis contains SUM(YTD Value)

If the parameter is chosen to be "Show" then I have to show a reference line which is the Avg of the YTD Value. If the parameter is chosen to"Hide" then the reference line disappears.

How do I accomplish this?

• ###### 1. Re: Drawing a AVG Reference line based on a a parameter

Hello Yunus

When using parameters you need to create the parameter, show it in the view, use it in a calculated field and add that field to the view.

I've attached an example workbook with those 4 stages.

1. Show Average Line parameter

3. Average Line calculated field

4. added calculation to detail shelf and created reference line from it

Let me know if that doesn't solve your problem

Steve

• ###### 2. Re: Drawing a AVG Reference line based on a a parameter

Thanks Steve for the quick response. Will implement it in my worksheet.

• ###### 3. Re: Drawing a AVG Reference line based on a a parameter

Hi Steve

Now if I want to take this a step further and show reference bands based on a parameter, then how do i go about it?

How will I be able to control the top and bottom reference bands with one parameter?

similar to drawing below.

I have a param, show bands and they have to show the two reference lines.

• ###### 4. Re: Drawing a AVG Reference line based on a a parameter

Hello Yunus

In the same way that we created a calculated field for the average line to only show results of our calculation if the parameter is set to "show", we need to do that twice for each percentile line, here is an example for the 90th percentile (using the WINDOW_PERCENTILE() function):

Then you need to add this calculation and a similar one for the 10th percentile to the detail shelf on the marks card then right click on your Y axis and add reference line:

Set the value to your percentile calculation, change the label to something sensible, then for the 90th percentile select "fill above" and click OK.

Repeat this with your 10th Percentile calculation but for that reference line select "fill below".

As both reference line calculations are dependent upon the parameter setting, when you switch to "hide" they should not appear.

I've attached a workbook, let me know if you can't get this working with your data

Steve

1 of 1 people found this helpful
• ###### 5. Re: Drawing a AVG Reference line based on a a parameter

Hi Steve

Thanks.

I had to do Std Deviation so I used the concept you provided and made two sheets, I then overlay one on top of the other in the dashboard depending on what parameter value says "Show". I have two parameters Show Bands and Show Avg Line.